table design advice needed

[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



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.



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
-------------
With this option company and job addresses would be stored in COMPANIES
and JOBS tables.

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...

 

[Craig Alexander Morrison]

Use the model which best represents your problem domain.

You state that the second more closely resembles your environment, then
choose that one.

If it does not meet it exactly then modify it until it does.

Always avoid overly complicated models as they may be modelling a much more
complex model, such as the universal reality which is rarely required for
most business purposes.

 

[mscertified]

It's also important to design systems to take into account possible future
modifications (which always occur) and not just to design for the present
moment.
That's what caused all the Y2K problems: short-sightedness.

-Dorian

 

[Craig Alexander Morrison]

Unless the future modification is probable it is a waste of resources to add
complexity that is not required. One can always revisit the design and
upgrade the system.

If you design systems to take account of all possible scenarios you could be
spending more time in total on the initial development than you would if you
created the system your users required now and had a major upgrade every
couple of years to take account of actual changes in requirement.

Modelling address data for use by a Local Club will be vastly different than
for a Logistics Company so I would only use the Logisitics model once the
local club was competing directly with UPS worldwide.

I would always advise against modelling reality one should model the problem
domain that applies to the user. During the development one would be
pointing out the possibilities of a wider model, but the solution should
model the users actual requirements at the time (and in the near future say
within 1 to 2 years if they are known).

As to Y2K there were very few problems and the amount of money saved with
this measure between 1960 and 1990 was astronomical. Most builds after 1990
took account of Y2K. Many systems that were actually a problem in Y2K were
systems that were not expected to still be around.

BTW what about the Y10K problem for systems that store the year as only a 4
digit number? You do know that many systems developed using a Jet or SQL
Server Backend will fail to operate on January 1, 10,000. How shortsighted
of Microsoft is that?

I take your point that a foreseeable future requirement should be
considered, however, only the probable ones in the expected lifetime of the
system (rather than the possible) should be actively considered for
inclusion in the model.