Create two tables with identical superkeys?

Question

I have an existing table called Realms. This has the superkey rlmID (just a surrogate key). We are planning to add tables that group realms in some context -- let's just calll them RealmGroups. This will require adding a realmGroupID column to Realms. No problemo.

Not all Realms will be part of RealmGroups, so Realms.realmGroupID would be nullable. In this case, is it better to have a separate table (RealmsRealmGroups) that has (rlmID,realmGroupID)? Since the superkey of Realms and RealmsRealmGroups would be the same, I see no advantage to creating a separate table, but my DBA is suggesting that we should use a separate one for organizational purposes.

Are there any technical or practical reasons to use a separate table to store information for the same superkey?

Answer 1

A separate table would give you the flexibility of having realms that are in more than one group. If it makes sense to do that in your business context, or it might be something you would want in the future, then you might want to seriously consider that option. Otherwise, having the Realms.realmGroupID as nullable is simpler and easier to work with.