From: dm <dm@anon.com>
Newsgroups: comp.databases.oracle.misc,comp.databases
Subject: modeling question
Organization: aaa
Message-ID: <22ohgvkqmjghmqbkmvriupgdjnklkfurjn@4ax.com>
X-Newsreader: Forte Agent 1.8/32.553
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 47
Date: Mon, 07 Jul 2003 03:19:06 GMT
NNTP-Posting-Host: 68.1.150.93
X-Complaints-To: abuse@cox.net
X-Trace: news2.east.cox.net 1057547946 68.1.150.93 (Sun, 06 Jul 2003 23:19:06 EDT)
NNTP-Posting-Date: Sun, 06 Jul 2003 23:19:06 EDT


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.