Baseline Conceptual Models: Product Specification Model

Product Specification Model:  Products are goods and services that a company provides.   This model tries its best to include cloud based products and services as well as your basic product line.  Its not bullet proof but will get you along way in thinking about how you might model your product line.

Goods: Goods are physical things in which a customer can lay their hands on like a device.   They can also be a card a person can buy that has a product key on it the activate features on a given product.  They can be a gift card that represents monetary value that can be applied to a subscription or a product purchase.  They can also be a digital asset that the customer can download and store on their device.  Goods are made up of physical parts or digital files.

Services: Services are more complex.   A service can provide a range of access to products, infrastructure, expert labor, trouble shooting, and education to the customer.  They can represent a product or technology infrastructure as a service such as a cloud service like Azure, Google, Facebook, or Amazon.

Product Relationship:  A product can relate to other products like being optional accessories to a main product.   They can be products relating as part of the same family of products.  They can be competing products if your a store caring competing products.

Product Keys: See Product Key Unlock and Activation Model
  
Digital Benefit Specifications:  Products may have one or more digital benefits that can be assigned.  Each benefit can be predefined and assigned to one or more product. 

• Metered Benefits are benefits that are metered when used such as processing time or storage space.   
• Client Licenses are benefits that enable a cloud service to provide seats to a service.  This is different then product keys in that client licenses can be as simple as registering an party as an account member to an account and granting them entitlement to a seat.  
• Download Benefits are benefits that entitle a customer to download a given set of digital assets such as products, documents, etc...
• Feature Benefits are benefits that entitle a customer a set of cloud side or client site features.

Note:  In this model a part can be a product.   But please note that a part can be made of one or more parts in the part bill of material (BOM).   And a product can be bundled with other products in an offer.  Please see Part Bill of Material (BOM) Model  and Product Offer and Pricing Model.

Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.

Baseline Conceptual Models Commentary

I am providing a series of conceptual models on this blog that will present the basic concepts that any business will need to master.   This is a best effort on my part on my personal time and you will probably run across flaws.  It is also a living work in progress and may change from time to time when I have time.  These models are not meant be perfect, but are presented to enable people to quickly take and modify for their own business needs as each business is different.  These conceptual models will only show the entity and their relationships with a suggestion for the primary key.  No attributes are given as they are at a conceptual level and may vary depending on the business.   But in my opinion attributes are easy to add after this stage.

Model Type Definitions:
Conceptual Model: A normalized model that is designed to clearly define the business entities that a business understands and their relationships between them.   This is to be at a high level and should not cover low level domain entities.  They do not include attributes, but in this case I'm at least providing the suggested Primary Key.  I believe that understanding the definition of an entity is aided by understanding what makes up the Primary Key.  Conceptual models are targeted for business people as well as engineers.

Logical Model: A normalized model that takes a conceptual model down further to the low level domain entities as well as formally defining the Primary Keys and assigning attribution for each entity.  Logical models are targeted for engineers mostly as I find business people get overwhelmed.  I will not cover this territory in my models presented on this blog for now as details may vary greatly between companies.

Physical model A schema that satisfies the logical models specification in physical database.  The schema is configured and tuned specifically to the environment in which it is targeted for.  Physical models are targeted for engineers.  I will not cover this territory in my models presented on this blog as its too specific to technology.

Entity Box Legend:  Blue boxes are master entities.  Light colored boxes are relationship entities.  Box in box are subtype entities.  There really isn't much meaning other than that.  I felt it useful for me.  You can ignore if you desire.

Disclaimer:  Take these models and use them at your own risk. 

Baseline Conceptual Models: Account Member & Profile Model

Account Member and Profile Model: Cloud Services need to have the ability to have members associated to an account in which they can use the service being provided for an account, but do not own the account itself nor have any financial responsibilities for paying for the service being provided.   This model also enables profiles for each member of the account.   This model depends on the Party and Account Model. 

Note 1:  I've enabled the ability to create a profile at the account member level rather then the account level or the party level because people may have different profiles for each team the may be working with.  Some more private then others.  Example:  I could be a member to 3 different Accounts.  One is my employer, another is a very public group I'm volunteering work for, and a private account in which I also own as well as being a member.    Each one of these profiles may be different depending on the focus and exposure.  I hope that clarifies where I'm coming from.

Note 2:  There could be multiple types of profile documents that could be created for a member.  One could contain more PII information that is used by the service and needs to be tightly secured and managed.   Another profile could be a gaming profile in which is more public and has no PII information. Physically speaking you could end up storing these two types of profiles separately due to security issues.   But I've placed them together because logically they are the same concept.

Note 3:  Attributes can be defined once and then applied to one or more profile documents to save from having to duplicate and manage multiple attributes that are logically the same concept. 

Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.

Baseline Conceptual Models: Contract Model

Contract Model:  Contracts are the staple for any business.   Service Level Agreements (SLA), End User License Agreement, and Pricing Agreements are just the beginning of the types of contracts people and companies will enter into to do business.   This model provides the understanding of participants of the contract and the beneficiaries of the contract.   These two concepts are important as companies can be owned by a parent company in which signs a contract with you that benefits all the subsidiary companies it owns by may not have participated in signing the contract.   The model depends on the Party & Account Model.   

Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.

Baseline Conceptual Models: Location Model

Location Model: Locations are logical or physical spatial maps that can represent a site, store, warehouse, dock, floor, room, or a bin in a room.   It can also represent a logical concept as digital locations.  From an enterprise modeling perspective I mostly use location for inventory purposes.  This model depends on the Party Model.

Note: There are some stranger concepts of locations in which represent in between locations in which inventory is on a truck going from place to place.  I call that location as "In Transit".  This location is usually used in inventory control.   Inventory can't be removed from a company inventory when your redistributing the inventory to other locations.  Inventory that is in transit still needs to be accounted for as inventory and be given a location of In Transit.

Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.

Baseline Conceptual Models: Party & Account Model

Party & Account Model:  Accounts are a vehicle to track various account activity, settings, entitlements, sales, subscriptions, promotions, and authorization to products and features.   The model works with the Party Model.  Its important to note that this strength of this model resides in the idea that a Party can have many Accounts.  Example:  Company "A" be a Vendor to you in which they provide services.   They can also be a Customer as well as a Partner to you.  Each role the have with you results in an Account in which track sales, purchases, entitlements, transactions, etc... relating to that role.   Accounts are used heavily by companies and is a critical component in your enterprise model.
    
Note 1: This model shows a Authentication Provider Account which follows a push for "Claim Based Authentication" technique for cloud based user logins.

Note 2:  You will notice that the Account Contact Point Entity has a suggested Primary Key of Account ID, Party ID, Contact ID in which Party ID is BOLD.   Party ID is an example of key folding in which the Party ID from Contact Point folds into Party ID from the Account Contact Entity.  I personally like to call this a data harmonic.  Hope that helps you understanding some advanced modeling techniques that are demonstrated in this model.

Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.

Baseline Conceptual Models: Party Model

Party Model: Tracking people, companies, and internal company groups along with their relationship to one another and their contact points is an absolute need for any one.   This model presents the standard concept that you will see in the modelling world called the 'Party Model'.  Its important to have a generalization concept the covers both person and organization called the Party because you will come across many business concepts in which either a person or group can have a relationship with.   Examples are Contracts, Memberships, Services, Products, Sales, and Shipment.    This will enable a relationship to just relate to the Party entity rather than making a relationship for both Person and Organization.  From a conceptual modeling purposes this makes the model easy to read which is the main point of what conceptual models are for.  But generalization concepts may not necessarily be the best design from a physical performance perspective if the generation results in a hot table becoming a bottle next in the system.   The needs of the physical world should prevail.

Note 1:  In this model Contact Points only can exist if they are associated with a party.   But if you are a postal service the address is important all on its own and can exist without a party.  So you will need to make the necessary modifications.    But that usually is a corner case from my experience.

Note 2:  This model demonstrates role naming:  Person ID from the Human Resource Entity is really a role name for a Party ID from the Party Entity.  Organization ID from the Human Resource Entity is really a role name for a Party ID from the Party Entity.

Note 3:  You will notice that the Human Resource Contact Point Entity has a suggested Primary Key of Organization ID, Person ID, Contact ID in which Person ID is BOLD.   Person ID is an example of key folding in which the Party ID from Contact Point folds into Person ID from the Human Resource Entity.  I personally like to call this a data harmonic.  Hope that helps you understanding some advanced modeling techniques that are demonstrated in this model.

Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.

Baseline Conceptual Models: Scheduled Event Model

Scheduled Event Model:   Events, Anniversaries, Holidays, Vacations, Meeting Schedules, Pipeline Execution Schedule, and Appointments are just a few types of events people, companies, or machines need to track.  This conceptual model works with the Calendar Model and Party Model to cover the ability to create an event calendar someone can add to there personal calendar to show the scheduled events for a given subject area, group, or person.  Example:  National Holidays, Company Paid Holidays, Company Wide Events, Personal Vacation Schedule, Birthday List, etc....  In application I can add to my personal calendar the list of national holidays for the U.S., the Company Paid Holidays, Company Events, and the Pipeline Execution Schedule to keep me informed.  

Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.

Baseline Conceptual Models: Calendar Model

Calendar Model:  Calendars are ubiquitous and required by any society.  Usually there are two calendars for a medium-to-large business: Gregorian and Fiscal.  This model should allow a business to create any type of calendar including Gregorian, Fiscal, Chinese, Hindu, Hebrew, or Astrological.   It does dictate that you should always have a year, month, week, and day concepts with an optional quarterly concept. 

Note 1: The Quarter concept could be based on a 13 week cycle(52/4) for each year or could be based on 3 month cycle (12/4) for each year.

Note 2: The Reserved Name Space is pretty straight forward as it represents for any type of calendar the names for each month and the names for each day of the week.  But the year name space may sound weird until you think of Chinese or astrological calendars in which the year name of a name of a constellation.  Example:  Year of the Dragon.       

Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.

How To: Sequence SQL Tables based on Dependency

There are many times in which you may need to know the sequence of tables based on dependencies in order to process data without foreign constraint issues and without turning off those constraints.  You'll need a list of tables from the most dependent table to the least dependent table.   The process below provides an example in how to provide such a list.


CREATE FUNCTION dbo.GetTableSequence ()
RETURNS @Temp2 Table
(  Sequence int Primary key Identity
 , ObjectID int
 , SchemaName sysname
 , TableName sysname)
AS
BEGIN
DECLARE @Temp1 Table
(  ForeignObjectID int
 , ForeignSchemaName sysname
 , ForeignTableName sysname
 , ObjectID int
 , SchemaName sysname
 , TableName sysname)

-----------------------------------------------------
-- Identify Tables with Depedency on another table
-----------------------------------------------------
Insert into @Temp1 (ForeignObjectID, ForeignSchemaName, ForeignTableName, ObjectID, SchemaName, TableName)
Select Distinct O.Object_ID as ForeignObjectID, Cast(schema_Name (o.Schema_ID) as sysname) as ForeignSchemaName, cast(o.name as sysname) as ForeignTableName,
                o2.Object_ID as ObjectID, Cast(schema_Name (o2.Schema_ID) as sysname) as SchemaName, cast(o2.Name as sysname) as TableName
From sys.foreign_keys f
Join sys.objects o
 on f.referenced_object_id = o.object_id
Join sys.objects o2
    on f.parent_object_id = o2.object_id
Where O.Name <> O2.Name -- Exclude Child-Parent Relationships (These causes infinite loops)
---------------------------------------------------------------------------------------
-- Recursively Identify Tables from Most Dependent to least Dependent
---------------------------------------------------------------------------------------
;
with RecursionCTE (ObjectId, SchemaName, TableName)
as
(-- First Get all tables that are not dependent on any other table. (Table can depend on them but not the other way a round)
SELECT T.ObjectID, T.SchemaName, T.TableName
FROM (Select   Distinct O.Object_ID as ObjectID
    , Cast(schema_Name (o.Schema_ID) as sysname) as SchemaName
    , Cast(o.name as sysname) as TableName
  From sys.objects o
   Join sys.columns c
     on c.object_id = o.object_id
  Where o.type = 'u') as T
LEFT OUTER JOIN sys.foreign_key_columns sfk
   ON sfk.referenced_object_id = T.ObjectID
Where sfk.referenced_object_id is null
union all
--  Now recursively work through all dependencies
select R1.ForeignObjectID, R1.ForeignSchemaName, R1.ForeignTableName
FROM @Temp1 as R1
join RecursionCTE as R2 on R1.TableName = R2.TableName
)
Insert Into @Temp2(ObjectId, SchemaName, TableName)
 select ObjectID, SchemaName, TableName
  from RecursionCTE
;
----------------------------------------------------
-- Delete Duplicates but keep the very last entry
----------------------------------------------------
WITH Dubs(ROWID, RecordID, SchemaName, TableName) AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY TableName ORDER BY Sequence DESC) as ROWID, Sequence, SchemaName, TableName
FROM @Temp2
)
DELETE FROM Dubs
WHERE ROWID > 1;
RETURN
END