Identifiers in a Diamond Relationship between Tables

Question

I have four tables (A,B,C,D) where A is the parent of one to many relationships with B and C. C and D are parents to a one to many relationship with table D. Conceptually, the primary keys of these tables could be:

• A: Aid
• B: Aid, bnum (with foreign key to A)
• C: Aid, cnum (with foreign key to A)
• D: Aid, bnum, cnum (with foreign keys to B and C)

Where the 'num' columns auto increment based on each parent id in the relationship rather then on each record. I used this approach on a previous application, and it was not an issue since the creation of B and C records was done by a sequential process by generating a new 'num' value via a 'select max()' query. I was never really satisfied with the approach, but it got the job done.

For the specific case I am working on now, records in tables A and B are entered by users so auto-generation of id's is not an issue. In the case of tables C and D, records in these tables are being generated by multiple concurrent batch processes so their identifiers will need to be generated some how. The previous method I listed will not work do to the race condition.

Note that this is for an Oracle database so I will be using sequences and not auto-increment columns.

Given the constraints above, how you would you design tables to represent A,B,C, and D so that the relationships between the entities are properly enforced AND application code would not be required to generate any identifiers?

Answer 1

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 returning "select @@identity" from the stored procedure to give the app the ID of the inserted row.

Sequences are great for primary keys imo, but there are camps that worship the god of 'natural keys'.

The meaning of the data stored in the table, and the meaning of the relationship is important to answer your question fully, but relationships can allow for cascading deletions.

Personally, I would make the primary keys sequences in each table and allow for foreign keys that are not part of the primary key. You would define your tables by the major objects (like employee, merchandise, store), and then the relationships between them would be made up of combinations So an employee in a store would have a table 'storeemployee' and the primary key would be empid, storeid with no sequence defined. Normally I think about it in terms of things as objects (which always have sequences for primary keys), and relationships between objects (with use other table's IDs as combo-primary keys).

Hope that helps!

Edit: I should add that this nicely allows for diamond relationships. Think about 'stores' and 'employees'. One table can be storeemployees, and another can be 'storesales'. Both would identify a store and an employee, but they mean drastically different things. One is maybe hours worked, and the other is sales made.

Answer 2

If I understand it right, you had a solution where you may 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

etc.

Now, does it matter whether the 'num' values are small and in a gap-less sequence? If not, then simply use sequences for those too. So you may get

Table B
-------
100 29125
100 29138
101 29130

Table D
-------
100 29125 401907
100 29138 404911
101 29130 803888

I'd use separate sequences for bnum and cnum. On selecting you could (if desired) 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