Please see above a new Table for Status
Firstly, is this in the correct schema ? as it is set to manage the status category of each of the d_dimension tables
The layout is of my own devising, but is set to be the way I would create any entity/dimension and is of a form used in VIP
The only difference in this incarnation is [parent_id] is replaced with [parent code], as we do not have a table of tables (that would be the source) … the parent manages the primary table hierarchy
Source_ID : this is expected to be the source file or originator
Parent_ID : as above this manages the primary table hierarchy
Linked_ID : This is to manage any internal table hierarchy (Default 0)
Created_Date : What it says on the tin
Modified_Date : What it says on the tin
Authored_by : currently a bit of free text to give an indication of the origianl owner … in VIP this defaults to ‘system’ as they are created by SP
Sort_order: What it says on the tin, useful for lookup menus
Item_status : This is intended as the normal status column for an entity, here it is being used for ‘reportable’
The Meat & Potatos
Item Code … this is unique, and in this table clustered with parent code
Item Description … What it says on the tin
Item Label … how you wish to see the the item displayed
I believe this is all that is required to describe anything…
I have used ‘item’ here, which makes it a bit anonymous, as it is useful for rapid coding of entities in the future i.e. JOIN enity.project as proj on proj.item_code = left.name
If you do not have an opinion, this is what you will receive from me in the future if you ask me to create an entity/dimension