• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Database design question

 
J Norm
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This isn't really a Java question, more about how I should design my tables.
I have a table called panel. The situation is that a panel can contain multiple panels. And a panel can be contained by multiple panels.
So far, this is the schema (below) I've come up with. The thing I don't like is having the two relationships between the panel and accumulated_panels table. It seems so goofy, but I can't think of another way to allow that two-way relationship.
With this schema, I could say select containing_panel from accumulated_panels where contained_panel = XXX. That would get me every panel that XXX is part of. Or I could say select contained_panel from accumulated_panels where containing_panel = YYY. That would get me all of the panels contained by YYY.
Is there a better way?
 
Loren Rosen
Ranch Hand
Posts: 156
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It looks like you're using the standard technique for handling this in a relational model.

You have a many-to-many relation between panel and itself. As with any many-to-many relation, you've got a third table that maintains the relationship itself.

Only question is whether your name for this third table conveys its purpose. DBAs often have specific naming conventions they want to use.

Of course, since this is the Object Relational Mapping forum we can talk about how to map these relational concepts into object concepts.

Also, if you expect frequently ask whether panel A is contained in panel B you'll want some additional indexes (or changes to the ones you have).
 
Brahim Bakayoko
Ranch Hand
Posts: 155
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Loren Rosen:


Of course, since this is the Object Relational Mapping forum we can talk about how to map these relational concepts into object concepts.

Also, if you expect frequently ask whether panel A is contained in panel B you'll want some additional indexes (or changes to the ones you have).


Yep, my advice is to use an object oriented approach to the design.
In Oracle, this will be easily implemented through objects and nested tables. Objects can contain other objects, so you can implement composition. Inheritance is limited to single inheritance and one level inheritance. Super types can be abstract (not instantiable) and/or have virtual methods (template methods).
 
J Norm
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for your advice guys.

Can anyone point me to a good Oracle forum? I can tell I'm going to have a lot of questions, as this is my first full-on Oracle project. I'm not a DBA, I'm a J2EE programmer, but I'm having to play the roll of the DBA for this project.

I don't like to post off topic if I don't have to, and there really isnt an Oracle forum here.
 
Mikey Warren
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Looks over complicated to me - but I wouldn't count my self as a database expert, so perhaps I'm missing something.

Couldn't you just have two tables ?
One with panel id as the primary key and all the things that go with a panel, like name, modified date etc.
One with two columns, "panel id" and "contained panel id" that would give you all the panels contained in each panel.

Also not sure what "panel entry" table is for.
 
Loren Rosen
Ranch Hand
Posts: 156
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There *is* an Oracle forum here, though it doesn't get a lot of traffic. In any case the question you asked above is basic relational modeling, nothing Oracle-specific about it.

Oracle's own forum's (at forums.oracle.com) may be useful, but I'm not sure how beginner-friendly they are.

Regarding panelentry and mm: I just ignored those tables in my comments above-- I assumed they were needed in the application but not relevant for the question at hand.
 
Marcus Pant
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I�ve worked just a little with the oo features of ora 8.1. Since then I prefer the "traditional" way of data modeling n:m relationships, because it is hard, even impossible to do schema evolution in a running system when using nested tables or object types.
 
J Norm
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The panel entry table is a bridge between panel and MRN, because there is a many-to-many relationship betweent them.

Those tables are irrelevant to the question at hand.

Thanks again for the advice. Now I just need to convince my boss that the simpler way is the best way to go.

-Jason
 
Fintan Conway
Ranch Hand
Posts: 142
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Jason,

As you are a Java programmer maybe you could convince your boss that the best way forward is to use JDO. It allows you to define the 'Objects' in your java code that you need to persist to the database. It does all the hard work storing tables on the database. This way you are coding in java and would only have to go to the database for tuning and things like adding indexes.

Regards,

Fintan
 
Darren Bell
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How many levels down does this go? I mean can a panel belong to a panel which then belongs to a panel? If so, this is the classic parent child relationship model. I have used this many times in ERP systems as this is used to store product structures (bills of materials).

Add a referential constraint from accumalated_panels.contained_panel_id which points to accumalated_panels.containing_panel_id.

This set up would then allow the deletions of entire structures through a cascade delete and also enforce that a child must have an entry on the panel table.

Another way is to place the parent panel ID on the panel table itself and do away with the accumalted_panels table and add the self referencing constraint to the panels table. This nicely enforces the rule that a panel can be a child to a single parent at a time, but a parent can have many children referencing it.

I hope this makes sense.

Darren.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic