Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Database Design - Relationships

Database Design - Relationships

Database Design - Relationships


I'm designing a database that contains a Homesite table (stores info about a new home) which will have RealitorID, InspectorID, ServiceRepID, etc. as attributes.
These are all foreign keys to a contact person.  I have a table (Contacts) which contains info about a contact (name, phone, etc) and a related table ContactRole table which stores 1 or more roles for the Contact Person (Salesperson, Customer Service, etc).
My question is this:
Should I lump all the contacts into one table and have the Homesite table relate to the Contacts table multiple times (for example: RealitorID, InspectorID, ServiceRepID would all be foreign keys to the Contact table (which has ContactID as the primary key)?  Or should I split the Realitors, Inspectors, etc. into their own tables?

I realize I would have to join the Contacts table multiple times in queries to pull all the info for the Homesites table ... would this cause major issues??

If anyone can give me suggestions on the best approach (those listed above, or a better way) I would GREATLY appreciate it!

Thanks in advance!

RE: Database Design - Relationships

I would store them in different tables. Reasons:

Readability, if another person have to maintain or modify the database it is easier to get the whole picture of the database structure.

Coding, with different funcionalities in the same table, you must instruct your application to do special processing if you need only a list of inspectors.

Scalability, if you need to upscale or transport to another database it is easier

S. van Els

RE: Database Design - Relationships

svanels, thanks for the reply!

I agree with each of the reasons you've listed.  So if you have a chance I would like your opinion on this design (let me know if this is what you had in mind):

A Person table (super-class) with all the attributes relating to an abstact person (name, phone, etc.), and a table for each of the various roles (Inspectors, etc sub-classes that contain the attributes relevant to that particular role) with a foreign key to Person.

My goal is to manage all contacts from the same interface.  I suppose once a contact person is selected I could query each of the sub-class tables for the ContactID and fill a combo or list box with the roles the person has, and then fill a grid with the attributes for that role once it has been selected from the combo box.  Or I could require selection of a role first, then select a contact, etc.

I should really take the ER model and "turn the crank" to convert it to a relational schema, but I'm working with an existing DB and I'm trying to balance a good design against going back and reworking existing interfaces.  I know the "official" answer is start with a good design at all costs, but of course in the real world you have to deal with time and cost constraints.

Once again, thanks for your help!

RE: Database Design - Relationships

I do exactly that..  upwards of 10k contacts(persons) in a contacts table(your generic person table) with foreign keys to the associated companies. It's a bit more complicated than that because the companies are members of company type tables. Maybe 7k+ companies.

John Howley

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close