Re: Table design question

[Preston McMurry]

The two tables have a many to many relationship, but the number of "manys" is open to question. Putting some set number of fields in CustomerType does two bad things: 1) waste space, for each customer which uses fewer than that number of types, and 2) not enough space for customers which have more than that number of types. It is poor design. :-)

What should instead do is have a "joining table" (the term I use, there may be a better one), which contains CustID and CustTypeID as foreign key fields. Call the table "foo". There can be no more than one foo record per combination of CustID and CustTypeID. (A customer cannot have the same type twice.) Thus Customer will have a 1-to-1(or many) relationship with foo; CustomerType will also have a 1-to-1(or many) with foo.

http://prestonm.com ... http://www.mcmurry.com