 | |
Re: Design question - when to generalize/specialize?
comp.databases comp.databases
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Re: Design question - when to generalize/specialize? -
08-06-2003
, 03:41 PM
|
We are designing the object model for a new system. But we have a legacy data model which stores both orders and order items in the same table. The relationship (between order and order items) is maintained through a self foreign key to the same table. We are discussing possible alternatives to this model. The developers of the old system say that the problem with storing both orders and order items is the complexity of the SQL code (with self-joins) and maintenance over time. To the legacy model's defense, order and order items do have some common attributes and similar life-cycles (like an order item could be tracked in its separate workflow [as an order would be tracked]). I agree it would be cleaner to separate the order item into its own table. But I would like to find about general perspective, suggestions on this issue. Which is better? What are the issues to be considered from the perspective of designing the object model? From the performance perspective? |
one order item (it needs at least one, though). They are not the same
entity, therefore they belong in separate tables.
If you need to count orders grouped by customer, for example, it is
much faster to use the smaller orders table than to group over all the
rows representing order items as well as orders. Besides, you might
need to use a criteria such as "WHERE price IS NULL" in order to
filter out the line items, making efficient use of an index
impossible.
Although designing a proper billing and inventory system is far from
trivial, I think separating orders from order items brings many more
advantages than disadvantages, even with legacy DBMS which don't have
the latest relational features.
--
Bob Hairgrove
rhairgroveNoSpam (AT) Pleasebigfoot (DOT) com
#2
| |||
| |||
|
|
An order item is not an order, although an order might contain only one order item (it needs at least one, though). They are not the same entity, therefore they belong in separate tables. If you need to count orders grouped by customer, for example, it is much faster to use the smaller orders table than to group over all the rows representing order items as well as orders. Besides, you might need to use a criteria such as "WHERE price IS NULL" in order to filter out the line items, making efficient use of an index impossible. Although designing a proper billing and inventory system is far from trivial, I think separating orders from order items brings many more advantages than disadvantages, even with legacy DBMS which don't have the latest relational features. |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.