Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> modeling question

modeling question

From: dm <dm_at_anon.com>
Date: Mon, 07 Jul 2003 03:19:06 GMT
Message-ID: <22ohgvkqmjghmqbkmvriupgdjnklkfurjn@4ax.com>


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.

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. Received on Sun Jul 06 2003 - 22:19:06 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US