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