Design Help

[Tansar]

Hi all,
I am creating a demo recruitment db and need some advice in knowing how to design it.

Basically, an employer (tbl_Employer) will most likely have more than 1 branch. Would I have create a separate table or do I include all the details in the main table with address, phone numbers and e-mails of each branch?

I bit confused with this...any advice appreciated.

TIA

 

[FoFa]

Depends on the usage, but since it is a demo it is not as critical. What should drive the descision is will this data be duplicated if I include it in the main table? If the answer is yes, than a seperate table is usually the better answer with a foreign key pointing to it in the main table.

 

[Tansar]

Hi,
Thanks for your response...

So, if I have Joe Bloggs & Co, based in London (HQ), and then Leeds, Newcastle and Glasgow

and then another co named Access Ltd in Leeds (HQ), and then also in Liverpool, Sheffield, Nottingham and Glasgow...

Would I have to create a table for each branch and link them to the main employer?

 

[neileg]

No, one table for Company names and one for Branches. Hold the ID of the company against the branch. Perhaps like this

TblCoName
CoID Autonumber Primary Key
Name Text

TblBranch
BranchID Autonumber Primary Key
Company Number Foreign Key from TblCoName
Address1 Text
Address2 Text
etc

Then pull together these two tables in a query, or a form and subform when you want to use them.