Meaningless Drivel is fun!*
The moose likes Object Relational Mapping and the fly likes database design help needed Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "database design help needed" Watch "database design help needed" New topic
Author

database design help needed

Venkata Kumar
Ranch Hand

Joined: Apr 16, 2008
Posts: 110

Hello All,

I am designing a database for my Java web application. I am struck in establishing relationships between tables.
Example database tables are given below

TABLE1
ID
GID
ATTR

TABLE2
ID (PKEY)
ATTR1
GID of Table1

TABLE3
ID (PKEY)
ATTR
GID of Table1


Table1 has composite primary key(ID,GID). Table1 ID is unique. Table1 GID is used to group different rows of Table1 and this GID can be mapped either to Table2 row or Table3 row but not both (one to one mapping)
When Table2 or Table3 row is deleted then associated GID entries from Table1 should be deleted. This is not mandatory.

How can i relate Table2 and Table3 to Table1. Please suggest me if we can represent data in other better way.
Any help on this is highly appreciated.


Thanks,
Venkat




SCJP 5.0, SCWCD 5, preparing for SCDJWS
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30965
    
158

It sounds like GID is a foreign key. The database will enforce that it exists in table 1 and isn't duplicated in table 2 or duplicated in table 3. It would not enforce the case where it is in both table 2 and 3. I think a trigger would be useful for this.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30965
    
158

Or are you asking how to do this using an ORM framework rather than in the database?
Venkata Kumar
Ranch Hand

Joined: Apr 16, 2008
Posts: 110

Hi Jeanne,
I am using Mysql workbench to design database. Once the design is over i will generate tables using workbench and map the tables to java objects using JPA annotations.

Workbench is not allowing me to create GID as foreign key in Table 2 or Table3 because GID is not unique in Table1.

I have another approach in my mind.

Instead of keeping GID in table2 or table3 let the ID of table2 or table3 be as GID in table 1. The tables look like below

TABLE1
ID
GID ( which is either ID of table2 or table3)
ATTR

(ID,GID) is composite primary key

TABLE2
ID (PKEY)
ATTR1


TABLE3
ID (PKEY)
ATTR

In this case whenever i delete Table2 or Table3 row then i have to explicitly delete the corresponding ID entries in Table1.

Is this better approach than the previous one?

Which approach is more suitable when it comes ORM. Basically when ever the Table2 row is retrieved using java object then corresponding Table1 entries(if exists) should be loaded from database and be part of Table2 java object.

Thanks,
Venkat
 
jQuery in Action, 2nd edition
 
subject: database design help needed