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?
(Note
atabase is Oracle and the new solution is going be J2EE)
Thanks.