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

Perhaps I mis-stated the issue, so I'll try again. SOME of the data is
abstractable as you suggest. The problem is that each DetailType
contains within it a bunch of columns that are irrelevant to other
DetailTypes. For example, a HotelRoom detail involves CheckInDate,
Duration (or CheckOutDate, whichever you prefer), Occupancy (Double,
Single, Triple, Quad -- and if it's Single then there's a
SingleOccupancyPremium to factor in). A ConcertTicket might be priced in
Platinum, Gold, Silver, etc. ranges, and have a row and seat number
attached. A CarRental might have a Size field (compact, mid-size, SUV,
whatever). And so on. 

So back to the two (or more models): 

A) Jam all possible fields into a single OrderDetails table, mask the
irrelevant ones using queries, and lose a lot of the built-in validation
stuff that Access offers.

B) Place all the stuff common to all DetailTypes in a single
OrderDetails table, which also contains a pointer to the N tables
corresponding to the collection of DetailTypes. Show only the common
fields in the subform; provide an Edit button or double-click or
whatever that opens the form corresponding to these fields unique to
each DetailType.

Does that help clarify the second question I was asking?

As to the first question, there is a term to describe a model such as
this:

Orders
   -= OrderDetails
	   =- DetailTypes
   Points to one of N tables that correspond to the collection of
DetailTypes

In this model, there is a term that describes the OrderDetails table,
whose basic function is to bridge the gap between Orders and the various
tables corresponding to the DetailTypes. Anyone know what this term is?

TIA,
Arthur


-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Porter,
Mark
Sent: Wednesday, August 11, 2004 4:30 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [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.