Understand schema

[donl1150]

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

[comment]

One table, two fields for types.

[HandOfCode]

If you're having troubles separating out entity information reading up on normalization is a great primer:

http://en.wikipedia.org/wiki/Database_normalization

[donl1150]

So it would be one table with these fields?

first name

last name

school name

church name

address

city

state

zip

phone

cust or pros

ind sch or churh

[HandOfCode]

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.

[comment]

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.