Using 'meaningful' field data to create relationships

Discussion created by on Apr 24, 2016
Latest reply on Apr 25, 2016 by electon


Lately I've been posting discussions about my planning and modelling while it's still mostly in my head.  I have found if I put my ideas. thinking and planning on a specific problem of data modelling or other topic as a discussion topic, I elicit really useful input from folks and this can help me avoid known pitfalls and really clarify my thinking.

Here's another one.


I mostly use UUIDs as primary keys and where possible build relationships on UUID based primary and foreign keys.


Now I have a scenario where data inputs are entered via a PHP file on a website receiving CURL posts with record data as JSON strings.

From this data the PHP file will create records in two tables, 'Order' and  'Item'.

One of these tables, 'Item' will need to be related to another table 'Product'.

A field in 'Item'  will determine which record in 'Product' the 'Item' record is related to.


My intention is to use a field 'SKU' in both tables and relate Item::SKU = Product::SKU

SKU's will be generated at my end - I can determine the structure and format of SKU's and these are handed to the ordering customer's system administrators to enter and include in for their  CURL posts to reference to any specific Product record.


If I use my Product::pkID (UUID) as the SKU then pkID has become 'meaningful'. I guess if anything changes in the future and SKU's need to be re-assigned or changed I am in big trouble if pkID = SKU.

SO my thought was to use a serial numeric field, auto entered just like a serial pkID field but not actually acting as the primary key (this is a UUID).

This serial numeric field can be my SKU to relate Items to Products.


This has the advantage of potentially minimising - data entry error as well I suppose as handing a data entry personnel a long list of UUIs could really open up some dramas if their doing it manually.


Any point doing a 'Lookup' in a hypothetical field Item::fkIDProduct, getting the product pkID from the relationship formed by SKU and then using Item::fkIDProduct = Product::pkID for any downstream TO = TO relationships etc.

Actually now I think on it this this could be used to  insulate the relationships from any future changes in SKU.


Welcome any thoughts or input.