Identifiers in a one to many relationship

Question

I have two tables, we'll call them Foo and Bar, with a one to many relationship where Foo is the parent of Bar. Foo's primary key is an integer automatically generated with a sequence. Since Bar is fully dependent on Foo how would I setup the primary key of Bar given the following constraints:

• Records for Bar are programatically generated so user input can not be relied upon for an identifier.
• Multiple processes are generating Bar records so anything involving a Select Max() to generate an ID would present a race condition.

I have come up with two possible solutions that I am not happy with:

• Treat the tables as if they are a many to many relationship with a third table that maps their records together and have the application code handle inserting records so that the mapping between the records is created correctly. I don't like this as it makes the database design misleading and errors in application code could result in invalid data.
• Give Bar two colunms: FooID and FooBarID and generate a value for FooBarID by selecting the max(FooBarID)+1 for some FooID, but as previously stated this creates a race condition.

I appreciate any ideas for an alternative table layout.

Answer 1

Give Bar an automatic primary key the same as with Foo. Add a foreign key FooID column to Bar.

Unless I'm missing something, there doesn't seem to be a reason why it wouldn't work.

Answer 2

from your description i'm assuming that your database does not support auto-increment identifier fields (MS SQL does, Oracle has 'sequences' which are just as good if not better, I don't remember MySql has).

If it does, then all you need is an auto-increment FooId and an auto-increment BarId, and Bar also has a FooId as a foreign key

If it does not, then you can create a single-row table for allocation as follows:

create table SystemCounter 
( 
    SystemCounterId int identity not null, 
    BarIdAllocator int 
)
--initialize SystemCounter to have one record with SystemCounterId = 1
--and BarIdAllocator = 0
insert into SystemCounter values (1,0)
--id allocator procedure
create procedure GetNextBarId ( @BarId int output ) AS
    SET NOCOUNT ON
    begin tran
        update SystemCounter set 
            @BarId = BarIdAllocator = BarIdAllocator + 1
        where SystemCounterId = 1
    commit
GO

note that if your database does not support the syntax

@BarId = BarIdAllocator = BarIdAllocator + 1

then you'll need to do it this way instead

begin tran
    update SystemCounter set 
        BarIdAllocator = BarIdAllocator + 1
    where SystemCounterId = 1
    select 
        @BarId = BarIdAllocator
    from SystemCounter
    where SystemCounterId = 1
commit

EDIT: I missed the Oracle tag originally, so Bill's solution is all that is necessary. Am leaving this answer as an example of how to do it in case someone is using a database that does not support identity or sequence constructs

Answer 3

Unless I'm missing something in your description, this sounds like an ordinary case. The usual solution is something like this:

INSERT INTO Foo (foo_id, othercolumn)
  VALUES ( FooSeq.NextVal(), 'yadda yadda');

INSERT INTO Bar (bar_id, foo_id, extracolumn)
  VALUES ( BarSeq.NextVal(), FooSeq.CurrVal(), 'blah blah');
INSERT INTO Bar (bar_id, foo_id, extracolumn)
  VALUES ( BarSeq.NextVal(), FooSeq.CurrVal(), 'bling bling');
INSERT INTO Bar (bar_id, foo_id, extracolumn)
  VALUES ( BarSeq.NextVal(), FooSeq.CurrVal(), 'baz baz');

The CURRVAL() function of a sequence only returns the most recent value generated by that sequence during your current session. Other concurrent use of that sequence doesn't affect what CURRVAL() returns in your session.

Answer 4

I can't quite see either, as per Ant P's and other answers, why which just generating a unique ID for bar and dropping Foo's ID in won't work. But supposing you're in a situation where auto-incrementing IDs are not available then there's two solutions that do not involve selecting max(barid)+1

1. Pre-generate a table of unique IDs and use a transaction to pull the next available ID from the table and delete it (as an atomic operation). This works fine but has the disadvantage that you have to keep the table populated.

2. Generate a UUID as the primary key. This isn't generally a good option as UUIDs are inefficient for this use, but it does have the advantage that no additional infrastructure tables are needed. UUID generators are widely available and some databases have them built in.