ericjlindholm

From Serial Keys to UUIDs... how does it change your schema.

Discussion created by ericjlindholm on Oct 9, 2017
Latest reply on Oct 11, 2017 by philmodjunk

I am embracing the UUID.   Ive read many repetitious articles about the major reasons to change. I know I can just replace a serial value with a UUID and do everything else the same but I am trying to leverage the concept in other ways.

 

I am looking how it might effect other things.  I am using a custom function that gets a value from the creation device, a time stamp and a serialized value of the second value of the timestamp.  I figure the same device can not create more then one record at any instant so I will assume that every uuid in my DB is unique even across tables.  Do you take this approach?

 

What are some of the ways it has effected your schema?

 

I realized today that any one to one relationship can just use the PK (uuid) of its parent as its own key.  even if those things are connected to multiple tables.

 

for example, a table for something like barcodes needs 2 fields.  id and valueBarcode.  I can create a 1 to 1 relationship from a barcode to any table in my file and set its id value to the UUID of its parent. I can use this to check for barcode value uniqueness when it is created and I dont need to create a PK and separate FKs for any table that it connects to because every id is unique.

 

I can do the same thing for a container table.  I can check the MD5 hash of a container for uniqueness to make sure my users are storing the same document in multiple places.

 

another example I am considering follows:

 

I currently have a task table that is connected to most other tables through a series of field like this:

id

id_purchaseOrder

id_contact
id_billid_equipmentList

and about 10 more.

 

This allows a user to make themselves a task about anything is a base table that makes sense.

The tasks table is relatively narrow already and im sure the extra 15 blank FK fields add little to no overhead.

The main task table has a TO for every task type so my TOs are listed as

 

TASK

task_purchaseOrder

task_contact

task_bill

and so on....

 

A an auto enter for the calc name looks like

 

case(

task::id_purchaseOrder ; task_purchaseOrder::displayName ;

task::id_contact ;  task_contact::displayName

and im sick of typing this. you get the idea.

 

A calc on a task table that put all the same Fks in the same field would have to evaluate if the relationship is valid for each of those items to auto calc a display value for the task.  does checking isValid on an empty related value take an equal amount of time as checking the fields in the same record for a value and only looking for a related record once that is found?  To me, that would be a reason to not store all the FKs in one field in a table that is one to many.

My primary goal is performance over the WAN so I want to leverage any little savings that I can.  But on this one, I have a feeling its a wash and the ease of setting the same field in the task table from anywhere might make it worth it.

 

I still get nervous about trusting the uniqueness and letting go of serial values. Extensive testing as showed me that ive got noting to worry about.  I even stopped checking my UUIDs for uniqueness because it doesn't check it across the whole DB so y bother adding the validation overhead.

 

sorry for all the poetic waxing about a new-to-me technique but its just got me thinking.  are they any other ways to leverage this global uniqueness?

Outcomes