How the heck do I ...

[gshomedesign]

I have a design issue that I need to address before building my tables. Anyone with experience please help.

I plan on having a table called Orders which is pretty basic. However, I will have different types of customers.
There will be web customers, realtors, builders, & designers. Each group will have the same information such as ID, name, address, etc.
The question is this, should I create One table that contains a customer_Id as primary key, and a cust_type (ie:bldr,realtor,...) as the secondary key? This would mean a pretty large table of information. But, the cust_Id would be unique.

Or, do I create seperate tables with primary keys in each table like: bldr_Id, realtor_Id,designer_Id, & customer_Id?

I originally planned on doing this 2nd method to keep things seperated, but now that I look at the Orders table, I cannot figure it to work. Because each different table will have identical Id numbers. If the primary key was able to generate a unique number along with a letter such as (B1) for builder or (D1) for designers, then I would not be worried and would keep things seperate. But, I am not experienced enough in MySQL to know if that is possible.

Could someone shed some light as to this issue?

 

[r937]

use one common table for all customer types

this makes relationships to other tables like Orders dead simple

within the Customers table, you may have a customer type column like you suggested

a complication arises when you have additional data items that exist only for specific customer types (this structure is called a supertype-subtype)

these additional columns typically get implemented as separate tables, but sometimes as columns in the main table, such that they are null for those customer rows for which they don't make sense (e.g. in your example, a web customer might have an email address, other customer types not) -- but you said all your customer types share the same columns so this supertype-subtype structure is really simple in your case

rudy

http://rudy.ca/