Jump to content

[SOLVED] Will this database structure work


dharm

Recommended Posts

Hi, i have designed a database generally without using auto instruments..

 

Users have an option to create and account from 2 different options "employee" or "employer". each will be given a 6 digit account number which is stored as a CHAR(6). when both members interact, for example exchange messages, the messages table will have an unique id called id_id CHAR(12) made up from both employeeID and employerID (000001000001).

 

Will it be ok to use the LIKE command to extract a message. so if i wanted all messages from a employee i would do this: WHERE id_id LIKE '".$employeeID."%'

 

i have tested the structure so far with a few members interacting but i would like to get reassurance that this way of identifying rows will work on a larger scale?

 

any advice would be much appreciated ..

 

thank you

Link to comment
Share on other sites

There would be an issue with searching against the employer ID since LIKE '%{$employer_ID}' would return more than expected.

 

Normally, linked tables are structured this way:

 

message.id

message.employer_id

message.employee_id

message.content

etc...

 

That is if employee to employee or employer to employer messages are not allowed, otherwise:

 

message.id

message.author_id

message.recipient.id

message.content

etc...

Link to comment
Share on other sites

Hi, bubblegum anarc, thanks for your response..

 

There would be an issue with searching against the employer ID since LIKE '%{$employer_ID}' would return more than expected.

 

so if i had rows like..

 

000001000001

000010000010

000100000100

001000001000

010000010000

 

and i search for

 

$employer_ID = "000100";

employer ID LIKE '%{$employer_ID}'

 

my return would be the one row 000100000100.

 

could you please tell me how it would get more results then expected?

 

Link to comment
Share on other sites

oops - you would not... sorry... I was mixing regular expression and MySQL LIKE.

 

well... the issue would then be in the amount of time taken to create a joins to employee and employer tables.

 

The combined id method requires that the ids be extracted before joins are created where as keeping id seperate does not.

Link to comment
Share on other sites

yes i agree..

 

Another point i figured was by using the combined ID's will limit the maximum amounts of entries. so my above example would limit me to 999999 entries only whereas separate ID's can be limitless

 

case closed (",)

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.