1

I am building a database that manages the definitions for a POS system (menu items, cashiers etc). It will include many stores and many versions.

So, typical table will look like this (MenuItems table)...

StoreID PK
VersionID PK
MenuItemID PK
MenuItemName

But I would like to merge the StoreID and VersionID into a single ID that represents the store and version (so, version 5 for store 101). So, I simplify the primary key on each table (because every table is going to need this)

And then have my tables looks like this...

SomethingID PK
MenuItemID PK
MenuItemName

Please help me name that StoreIDAndVersionID field. I have not idea what to call it.

  • 8
    Wait, are you encoding this new ID with two distinct pieces of information just to avoid having a two-column key? So when someone is looking for store 101 you have to perform some kind of calculation to find all the versions for that store? I don't think that is going to work out as well as you think. – Aaron Bertrand Feb 25 '16 at 17:50
6

You should not retain two (or more) different values, each from a distinct domain, within the same column, because that procedure would only introduce unnecessary complexity to the restriction, manipulation (insertion, modification, deletion and retrieval), general maintenance and interpretation of the data. See this relevant Stack Overflow post by @PerformanceDBA for some related aspects.

I definitely would keep each contextually individual datum in its corresponding, dedicated, constrained and meaningful column. So, in your scenario, I would set (a) one specific column to hold the StoreId and (b) one separate column to hold the VersionId in all the pertinent tables, such as you have presented in your first sample. This approach avoids the manual extraction and validation of each of the individual values contained in a combined column, which is very important since said operations would undermine the system speed and, most likely, would also eventually compromise the data integrity.

In case you want to merge those columns to meet some particular information display requirements, you can concatenate their values on the fly by means of a SELECT statement or, perhaps, with the aid of your application program code, and then show them inside a single column to your data users. Some feasible names for this computed or derived column could be, for instance, StoreId and VersionId, StoreId & VersionId, StoreId – VersionId, StoreId / VersionId or VersionId for StoreId, since these denominations would express the —purely expository— blend of two distinct data points.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.