Design Question
-
Hi,
I have a design question. Let’s say I have three tables. Table1, Table2 and Table3. Table1 contains three columns ID1, ID2 and Field1. The primary key of Table1 is made up of ID1 & ID2. Table2 and Table3 are similar in structure both contain an ID column specific to their respective tables and ID1 (from Table1).
There is a relationship between Table1 & Table2 and Table1 & Table3. In both cases, Table1 is the parent table and is related to ID1 on the other two tables. However, the relationship needs to specify that for any value of ID1 in Table1 there are zero or one related rows in Table2 (or Table3). Conversely, for any value of ID1 in Table2 or Table3, there is zero to many rows in Table1. It is important to note that ID2 has no relevance to Table2 or Table3. It is also important that I am able to get multiple values back from the parent table with a single value for ID1.
As you can see, this just looks plain wrong. It seems as though Table1 should be the child table in the relationships. But since it is the common table, ID1 must be generated there so it seems as though it should be the parent. It seems as though, my table structure is right but my relationships are backwards. Here is the layout of Table1 and Table2:
Table1
ID1 ID2 Field1
---- ----- -------
1 1 a
1 2 b
2 1 c
Table2 (or Table3)
AnID ID1
------ ----
25 1
26 2
Any thoughts? Thanks in advance.
Pete
-
Why are Table2 and Table3 separate, what is unique about them for them to be separate?
If they were one table your contraints would be easier to implement
Far away is close at hand in the images of elsewhere.
Anon. -
In reality I have several tables like table 2 & 3. Some have two fields others have more. In essence, I have many tables that reference table 1. Perhaps in the example I should have made a distinction that the "AnID" in table 2 is different than the "AnID" in table 3. The reason for not combining them is to preserve my ability to maintain referential integrity via primary & foreign keys. Sorry for the confusion. Here is the corrected drawing:
If table 2 & 3 were the same, your solution would make things much eaiser.
In light of that, any other thoughts? Anything would be helpful. Thanks for the reply.
Pete
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply