Database Design – Naming a Field That Includes Store and Version ID

database-design

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.

Best Answer

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.

Related Question