[dba-SQLServer] 2 Questions -- one about terminology, one about design choices

Arthur Fuller artful at rogers.com
Wed Aug 11 14:44:39 CDT 2004


I must have asked various experts this question a dozen times, but it
seems that wherever my brain chooses to file the answer must have been
afflicted by those joints I smoked as a youth :)

Actually, I have two questions here, the second dependent to some degree
upon the first.

Assume an Orders table. Assume several types of products/services that
can be placed upon said Orders table. I.e. instead of a single
OrderDetails table, there might be several such tables. A simple example
might help clarify what I'm getting at...

Order 123

FlightDetails -- departs, arrives, price, etc.
HotelDetails -- checkin, checkout, price, etc.
CarRentalDetails -- fromdate, todate, price, added insurance stuff, etc.
ConcertDetails -- 2 gold tickets to Madonna + Eminem, price, row/seat
numbers etc.
ExtraDetails -- leather jacket signed by Madonna and Eminem, price,
size, colour, etc.

Question 1: assuming that you have 5 such "detail" tables connected to
each order, and that you model this by creating one OrderDetails table
each of whose rows point to one of the 5 tables, what is the jargon name
for the OrderDetails table? There IS such a name, and this is not an
uncommon modeling problem, but for the life of me, I cannot remember the
name for this.

Question 2: In particularly broad strokes, I can envision this in two
ways. 

1. Jam the OrderDetails table with every field that any DetailType might
need; create queries that expose only the fields of interest to that
DetailType; then create forms based on those queries. Advantage?
Everything is in one table and certain fields are common to all
DetailTypes (Description, Price, ExtendedAmount, etc.). This allows easy
creation of the OrderDetails subform. To edit any detail, the user might
double-click on the row. The code examines the detail type and loads the
appropriate edit form.

2. Keep everything in separate tables, while the OrderDetails table
serves merely as a pointer to which table to look in for each row's
data. Advantage? No columns are irrelevant to any particular DetailType,
which enables much better table-level validation. Disadvantage? You have
to find some way to aggregate all the Details from 5 tables. Union could
do it, perhaps.

Opinions, anyone?

(In my years on this list, I have noticed no dearth of opinions :)

TIA,
Arthur




More information about the dba-SQLServer mailing list