Home » Developer & Programmer » Designer » Table Design issue
Table Design issue [message #229773] Tue, 10 April 2007 00:29 Go to next message
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
Re: Table Design issue [message #238651 is a reply to message #229773] Fri, 18 May 2007 16:25 Go to previous message
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------|        |
|...     |        |...     |
+--------+        +--------+

Previous Topic: Altering table design
Next Topic: Date Partitioning and Index
Goto Forum:

Current Time: Thu Dec 09 04:25:27 CST 2021