Drawing autogenerated values from same sequence on two tables

Question

Hello all,

I have two distinct sets of users in a database. I want to track whether each user has taken a survey. The two sets of users have no common attributes whatsoever. (That is, one set of users have an arbitrary id number, name, and sometimes email address. The other set have an arbitrary id number and email address. The only common element, the email address, isn't guaranteed to be unique or even present in the first set--two users might somehow share the same email in the first set.)

Let's say that the sets look like so:

   SetOne: SetOneId, Name, Email nullable
   SetTwo: SetTwoId, Email unique

Somehow, I need to create a "unified" key to track both SetOne users and SetTwo users. It was suggested that I simply have a mapping table that looks like

   UnifiedMap: UnifiedKey integer primary key, SetOneId integer nullable, SetTwoId integer nullable

and simply require that only SetOneId or SetTwoId be nonnull. I don't like the above solution, because I don't want to see a NULL in every record.

Is there some way I can have two tables?

   UnifiedSetOneMap: UnifiedKey integer primary key, SetOneId integer
   UnifiedSetTwoMap: UnifiedKey integer primary key, SetTwoId integer

where the two tables have no UnifiedKey field values in common. (That is, UnifiedSetOneMap.UnifiedKey INTERSECT UnifiedSetTwoMap.UnifiedKey is the empty set.)

I want UnifiedKey to be autogenerated. Somehow I'd like to specify that both tables should have their autogenerated UnifiedKey fields drawn from the same sequence.

For the sake of exploring the idea of sequences, let's say I can't use a GUID.

Ideally, this solution will be portable. However, in the likely event that it is not possible to make a portable solution, TSQL is what I'll be implementing this in.

What is the best approach for me to take? Should I just suck it up and use the first UnifiedMap idea and live with the NULLs?

Answer 1

For SQL Server DB I’d create primary keys as :

SetOneId integer identity(1,2) primary key;
SetTwoId integer identity(2,2) primary key

identity(seed, increment): seed is starting point, increment is step

Not all DBs have identity option, so I don’t know if it’ll work for you.

Answer 2

The UnifiedMap table approach certainly makes more sense.

If you wish to persue the two seperate UnifiedSet tables idea, you'd need to change the seed value of UnifiedSetTwoMap when an insert happens on UnifiedSetOneMap and vice versa using a trigger on each.

Like I said the UnifiedMap approach is simpler, and more elegant.

Answer 3

Sequence-based solution cannot be portable simply because not all databases support sequences.

That said, it's reasonably straightforward to use the "two separate tables" approach where both would use the same sequence for their PK values. Are you using some kind of ORM? JPA, for example, (or NHibernate in .NET world) would allow you to do this out of the box. If you're doing this manually you'd basically need to do something like:

INSERT INTO SetOne (UnifiedKey, ...) VALUES (unified_seq.NEXTVAL, ...);

for both tables.