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

Could you abstract the data?

example structure like:  

Product Type (Flight, Hotel, etc.)
Product FromDate
Product ToDate
Product Cost
Product Tax
etc.



> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Arthur
> Fuller
> Sent: Wednesday, August 11, 2004 11:45 AM
> To: dba-sqlserver at databaseadvisors.com
> Subject: [dba-SQLServer] 2 Questions -- one about 
> terminology,one about
> design choices
> 
> 
> 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
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
> 

***********************************************************************************
11/8/2004
This transmittal may contain confidential information intended solely for
the addressee. If you are not the intended recipient, you are hereby
notified that you have received this transmittal in error; any review,
dissemination, distribution or copying of this transmittal is strictly
prohibited. If you have received this communication in error, please notify
us immediately by reply or by telephone (collect at 907-564-1000) and ask to
speak with the message sender. In addition, please immediately delete this
message and all attachments. Thank you. ACS