From: andrewst Newsgroups: comp.databases.oracle.misc Subject: Re: modeling question Date: Mon, 07 Jul 2003 13:59:24 +0000 Organization: dBforums Lines: 68 Sender: andrewst Message-ID: <3081627.1057586364@dbforums.com> References: <22ohgvkqmjghmqbkmvriupgdjnklkfurjn@4ax.com> User-Agent: dBforums X-Original-NNTP-Posting-Host: 64.106.154.84 X-Original-Trace: 7 Jul 2003 14:36:44 GMT, 64.106.154.84 X-Authenticated-User: usenetgateway Originally posted by Dm > I must store inTransit information for a logistics application. My > application has a retail and wholesale side. > Items can be intransit: > from one retail location to another > from one wholesale location to another > from a retail location to a wholesale location > from a wholesale location to a retail location > > I have a table for retail locations and a table for wholesale > locations. These entities work different in the business process and > have different attributes so I think they should be modeled as > separate entities. > > How should I physically implement this? > > I see 4 solutions: > 1) The inTransit table has 2 columns for the FROM location and 2 > columns for the TO location. Ex. to_retail, to_wholesale, > from_retail, from_wholesale. > The drawbacks of this approach are that I have no way to enforce that > only 1 TO column and 1 FROM column have values. It is also not very > easy to add a new location type if one is needed. > You could do that with a check constraint: CHECK ((to_retail IS NULL and to_wholesale IS NOT NULL) OR (to_retail IS NOT NULL AND to_wholesale IS NULL)) But I'd go for option 4 below... Originally posted by Dm > 2) Have 4 version of the inTransit table. One for retail to retail, > one for retail to wholesale, etc. > The drawback is that when retrieving data I have to join 4 tables to > find out what is in transit. Also, if a new type is discovered I'll > have to add tables and update all of my SQL. > > 3) Have 1 inTransit table with 1 FROM column and 1 TO column. In > addition, I'll have type columns for each location. If the FROM_type > = 'Retail' I'll join to the retail table. > The drawback here is that I have mixed metadata with data. I also > think the SQL will be a problem because I'll have to join with a table > that is determined by the data. It can be done but performance may > suffer. > > 4) Move the locations into one table with a type column. Then my > intransit table only needs the to and from locations. > The drawback to this approach is that I will need columns that are not > used for some types. Of course I could break them out into another > table but then I'll have this question on that table. > > I would appreciate any advice you can give me. I would go for option 4. You can use check constraints to ensure that only appropriate columns are populated according to type. From a logical, analysis, point of view you have a supertype LOCATION with subtypes RETAIL_LOCATION and WHOLESALE_LOCATION. This can be implemented in various ways (as you have shown). However, since for some purposes you need to look at ALL locations together, a singe-table implementation is most efficient design. You might use views called RETAIL_LOCATION and WHOLESALE_LOCATION for convenience where only one subtype is applicable. -- Posted via http://dbforums.com