Re: modeling question

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