database design help needed

[Venkata Kumar]

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

[Jeanne Boyarsky]

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.

[Jeanne Boyarsky]

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

[Venkata Kumar]

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