HELP! with table design and relationship

G

Guest

Hi Everyone,
I really hope you can share some light on this as its driving me nuts!

I am creating a Contact Management DB which is supposed to be simple (in
principle), however i am seriously stuck on something trivial and wonder if
anyone can share their knowledge with me. I don't know where i am going
wrong with the table design and linking of Primary keys. May be i am even
missing tables, but i don't know how to proceed.

Usually we store contacts in a database (or even outlook for that matter) as
INDIVIDUALs. So if we have say....5 different people belonging to say the
same COMPANY we'll have to create 5 separate records for them (this is
straight forward yet very repetitive)

However what about when you want to record these contacts simultaneously in
the same record by perhaps having separate tabs for "COMPANY details"
(address, URL, Tel etc) and another one with "CONTACTS" (different first
names, last names, emails etc)?
I know it sounds simple, but i am actually stuck because when i design the
form with the 5 x First Names, Last names etc the first record gets
duplicated.

Can anyone help?
Thanks
 
A

Allen Browne

You do not *store* 5 individuals in the record for one company, but you can
*display* them that way (e.g. in a report) if needed.

If several people can be part of one company, you have a one-to-many
relationship, so you need a related table. If some people can also belong to
many companies (e.g. part time, or they move over different times), you have
a many-to-many relation between companies and people, so you need a junction
table.

Since your contacts can be persons or a companies, you need a very flexible
structure that copes with the various kinds of relationships that can exist
between them all. You can download an example of this kind of flexible
structure in this link:
People in households and companies - Modelling human relationships
at:
http://allenbrowne.com/AppHuman.html
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Table Design Question 3
help with table design for Code Library 2
Table Design Info 3
Table design 3
Relationship/Query Problem 7
Struggling with the design of this beast... 4
Table Design 5
Table/Forms Design Question 16

Top