Jump to content

Understand schema


donl1150

This topic is 4806 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I am trying to understand the concept of one-one, one-many, many-many. This seems to be critical as you develop the structure of your database. I am not quite certain how far you take the concept….does each field need to be unique to that table? Here is an example.

I have three types of customers….individuals, schools & churches. All have similarities, i.e. an address, city, state, zip, phone, etc. I also therefore have the same three types of PROSPECTIVE customers – those that I am hoping will become customers.

Would I therefore create six tables to capture their unique data (name, address, city, state, zip, etc which is unique to that individual, church or school)?

Or, would I create two tables for this data (one each for customers & prospects)?

Or, lastly is it best to only have one table that had all of their unique data + one field with the variable data (customer or prospect)?

Thanks

Don

Link to comment
Share on other sites

If there's no unique info differences between them yes. If any of the fields are unique to someone being a "cust or pros" or "ind sch crch" then you need to make a table for the unique "state" (ex. a schools table) and have a foreign key in it called "_ContactID" or something similar (that's just my personal naming scheme).

In your case you have "school name" and "church name" so you should have 3 tables. Otherwise you'll have blank fields and other issues. Also in that case the "school" and "church" table should have unique primary keys and unique addresses that are different from your contacts table. (assuming you have a "designated" contact for those (ex. the principal)).

If you read the normalization article you'll see that doing this will prevent update anomalies where for instance a school changes it's name or a church does, instead of finding all instances of the old name and changing it (and possibly missing one) you now have only one field in one record to change.

How you separate out the tables depends highly on what you're trying to do and what information you're concerned with.

Link to comment
Share on other sites

I believe you could use the same field for last name, school name and church name - or perhaps two fields, LastName and OrganizationName. With one field, when searching for "Smith" you'd find "Smith" (a person) as well as "The Mary Smith School" and "The Smithsonian Church". But you could also use a calculation field for this, so it's not critical.

I'd suggest you rename your table to Contacts - that should help you see it in the right perspective.

Link to comment
Share on other sites

This topic is 4806 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.