Identifiers in diamond relationships between tables
I have four tables (A, B, C, D) where A is the parent of one to many relationships to B and C. C and D are the parents of one to many relationships to table D. Conceptually, the primary keys of these tables can to be:
- A: Aid
- B: Aid, bnum (with foreign key for A)
- C: Aid, cnum (with foreign key for A)
- D: Aid, bnum, cnum (with foreign keys for B and C)
In cases where the numeric columns "num" are automatically set based on each parent ID in the relationship, rather than on each record. I used this approach in a previous application, and it was not a problem, since the creation of the B and C records was done in a sequential process by generating a new "num" value using a select select () query. I was never happy with this approach, but it did the job.
In the specific case I'm currently working on, records in tables A and B are entered by users, so auto-generating the ID is not a problem. In the case of tables C and D, the entries in these tables are generated by multiple parallel batch processes, so their IDs must be generated somehow. The previous method that I listed will not work in a race condition.
Please note that this is for an Oracle database, so I will be using sequences rather than auto-incrementing the columns.
With the above constraints, how would you create tables to represent A, B, C, and D so that relationships between objects are properly enforced AND application code is not required to generate any identifiers?
source to share
If I understood correctly, you had a solution in which you can have
Table A
-------
100
101
102
Table B
-------
100 1
100 2
101 1
Table C
-------
100 1
100 2
101 1
Table D
-------
100 1 1
100 2 1
100 1 2
101 1 1
and etc.
Now, does it matter if the "num" values are small and in a sequence with no spaces? If not, then just use sequences for them as well. So you can get
Table B
-------
100 29125
100 29138
101 29130
Table D
-------
100 29125 401907
100 29138 404911
101 29130 803888
I would use separate sequences for bnum and cnum. When selected, you could (optionally) use something like
SELECT AID,
RANK(BNUM) OVER (PARTITION BY AID ORDER BY BNUM) bnum_seq,
RANK(CNUM) OVER (PARTITION BY AID ORDER BY CNUM) cnum_seq
source to share
Sequences or Autonumbers should always be generated by the database system and not by the application. For MSSQL, this can be done using a stored procedure and return "select @@ identity" from the stored procedure to give the application the identity of the inserted row.
Sequences are great for imo primary keys, but there are camps that worship the god of "natural keys".
The value of the data stored in the table and the value of the relationship are important to fully answer your question, but relationships can allow for cascading deletes.
Personally, I would do primary key sequences in each table and allow foreign keys that are not part of the primary key. You define your tables with basic entities (eg employees, products, store) and then the relationships between them will be combinations. Thus, the employee in the store will have a table "storeemployee" and the primary key will be empid, storeid in no particular sequence. I usually think of this in terms of things like objects (which always have sequences for primary keys) and relationships between objects (using other table identifiers as combined primary keys).
Hope it helps!
Edit: I must add that this allows a diamond relationship perfectly. Think of "stores" and "employees". One table might be storeemployees and the other might be "storeales". Both will identify the store and the employee, but they mean completely different things. One of them may have worked hours, and the other was sales.
source to share