• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Bear Bibeault
  • Liutauras Vilda
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Jj Roberts
  • Carey Brown
Bartenders:
  • salvin francis
  • Frits Walraven
  • Piet Souris

Design question - when to generalize/specialize

 
Ranch Hand
Posts: 95
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 1923
Scala Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The rules of normalisation will lead to sepeate tables, since order and order_item represent different things.
To understand the reasons for putting them into the same table, I would need to know more Details.
 
Velmurugan Periasamy
Ranch Hand
Posts: 95
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for the response.
As I mentioned before, order and order item have some common attributes (so they wanted to use the same columns). Also order and order item have similar lifecycles - meaning that from the creation until the fulfillment they need to be tracked. An order item can be tracked individually.
Few other tables can have relationships with both order and order item. This is one more reason given for why they use the same table.
One more thing to mention is that the new requirement says order items can have sub-order items. (but not to infinite levels of depth)
Thanks.
 
author
Posts: 14112
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Most importantly, decouple the object model from the database model. They do change for different reasons, so it would be bad if a change in one would have a big impact on the other.
For example, just because the data resides in a common table doesn't mean that they can't be different classes in the object model.
Take a look at http://www.ambysoft.com/mappingObjects.html
[ August 06, 2003: Message edited by: Ilja Preuss ]
 
Velmurugan Periasamy
Ranch Hand
Posts: 95
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for the suggestions.
reply
    Bookmark Topic Watch Topic
  • New Topic