Basic newbie question about table layout.

[mac]

We need to set up a database to record the jobs we work on for our
clients and the names and addresses of the people/companies (clients,
contractors etc.) that work with us on these jobs.

Two people have come up with two different designs which I have tried
to illustrate below where L/T = link table.

[NAMES]<-->[L/T]<-->[ADDRESSES]<-->[L/T]<-->[JOBS]<-->[L/T]<-->(back
to)[NAMES]

[PEOPLE]<-->[L/T]<-->[COMPANIES]<-->[L/T]<-->[JOBS]
With this option company and job addresses would be stored in COMPANIES
and JOBS tables.

The first (circular layout) I think is too complicated but it does give
a lot of scope for jobs to have many names and many addresses,
addresses to have many names and many jobs and names to have many
addresses and many jobs.

The second is simpler in my mind as I can relate this better to the
real world as jobs can have many companies and companies have people.

I would be grateful to receive comments from the Access experts here on
which one we should go for.

 

[TC]

The critical thing that you need to achieve, when discussing table
structures, is to clearly show the primary key field(s) of each table.

Here's how I suggest you do that (using random table & field names):

tblCustomer
CustID (PK)
CustForname
etc.

tblInvoiceLine
InvoiceID ( composite )
LineNo ( primary key )
etc.

The informal notations that you are using, are too imprecise, IMHO.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[mac]

Hi TC

Sorry for not replying sooner I have been away because of family
matters.

Here are the two table designs in your fomat.

FIRST OPTION
----------------------

tblJobs
ID (PK)
JobNumber
JobName
JobDescription
etc...

tblJob-Names (Junction table)
ID (PK)
Job_Reference (ID From tblJobs)
Name_Reference (ID From tblNames)

tblNames
ID (PK)
Surname
Forename
Phone_Number
etc...

tblNames-Addresses (Junction table)
ID (PK)
Name_Reference (ID From tblNames)
Address_Reference (ID From tblAddresses)

tblAddresses
ID (PK)
Address_Line1
Address_Line2
Address_Line3
etc...

tblJobs-Addresses (Junction table)
ID (PK)
Address_Reference (ID From tblAddresses)
Job_Reference (ID From tblJobs)

SECOND OPTION
---------------------------

tblPeople
ID (PK)
Surname
Forename
Phone_Number
etc...

tblPeople-Company (Junction table)
ID (PK)
People_Reference (ID From tblPeople)
Company_Reference (ID From tblCompany)

tblCompany
ID (PK)
Company_Name
Address_Line1
Address_Line2
etc...

tblCompany-Jobs (Junction table)
ID (PK)
Company_Reference (ID From tblCompany)
Job_Reference (ID From tblJobs)

tblJobs
ID (PK)
JobNumber
JobName
JobDescription
etc...

Mark