Table Design issue [message #229773] |
Tue, 10 April 2007 00:29 |
cnaganathan
Messages: 1 Registered: April 2007 Location: India
|
Junior Member |
|
|
Here is a problem that one of my design team members has come up with.
Table A is a Reference Data table that uses an ID as the primary key and a column called Level which indicates what type of data is that. Table B uses this reference data.
One of my designers wants to have two columns in Table B both Foriegn keys referencing the ID Column of Table A. Both columns will be nullable and the ID value from Table A will be stored in one of them based on the value of Level in Table A. This way he feels that it will be easy to store and retrieve data from Table B.
Is this acceptable design practice? If not what is the best way?
Note: The Application is written in JAVA and uses a Struts like framework that we have developed in-house.
Thanks in anticipation
Chandru
|
|
|
Re: Table Design issue [message #238651 is a reply to message #229773] |
Fri, 18 May 2007 16:25 |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
Yes the structure is fine if I understand you correctly, but only you can decide if it's the right solution for your business rules. FK1, FK2 should ideally be indexed, probably as IX1=(FK1), IX2=(FK2, FK1)
B A
+--------+ +--------+
|ID_B(PK)| | |
| | | |
|FK1 |>0------|ID (PK) |
|FK2 |>0------| |
|... | |... |
+--------+ +--------+
|
|
|