Geek Answers Handbook

SQL architecture: Is it justifiable to have only one table storing several types of entities? (using stand alone JOIN)

I rarely come across a situation where one table for several types of entities seems better than one table for an entity type. Here is an example that makes sense to me, but in an academic sense, this seems wrong.

QUESTION: Can I do this and still have a “sound” architecture?

Example:

Suppose there are two types of entities, a corporation and a person. A corporation is usually owned by a person, but sometimes another corporation owns the corporation.

Holding on to this thought and adding to it, let’s say that every corporation has a registered agent who is responsible for the legal establishment of the corporation. Continuing my illustration, a registered agent can be either a person or a corporation.

If you think that the owner [parent] of the corporation [subsidiary] can be either a person or a corporation, you can start considering the problem of maintaining the third normal form and avoiding redundancy.

Unlike my example, if only people can own the Corporation, the Ownership link table is very conditional, having columns: OwnershipID (sort of unnecessary), CorporateID, PersonID.

Instead, you need something like: OwnershipID, CorporateID, OwnerID, OwnerEntityType (corp or person). Don't get me wrong, you can do this job, but at least it won't be fun.

, , . (). . , ( ). .

"" , , CPA - biz, . , -, - -. . [ , , CorporateEntity)

, () , , : AgentRepresentationID, CorporateID, AgentID, AgentType... , (IMO), - Person, .

: , . :

: EntityAll
: EntityID, EntityType ( EntityTypeID, , , ), EntityName ( ... )

:
Key Columns: OwnershipID ( , , ), ChildEntityID (, , "Child" , ) ParentEntityID ( )

: AgentRepresentation
: AgentRepresentationID (... ), CorporateEntityID ( corp), AgentEntityID ( Entity, , )

, . . , , (-, , , ). , , , , .

, , . EntityAll , , .

( T-SQL):

SELECT Corp.EntityName as CorpName, Owner.EntityName as OwnerName
FROM EntityAll as Corp
JOIN CorporationOwnership as Link on (Corp.EntityID = Link.ChildEntityID)
JOIN EntityAll as Owner on (Link.ParentEntityID = Owner.EntityID)

, , ().

, , , , .

P.S. SO. , . , .

/: db:

+5
3

, " " " ", (); " " , .

SQL-92 ASSERTION (, , CHECK, ), , ( ) SQL, - ( - , ).

, SQL, - (entity_ID, entity_type) CHECK entity_type , , ( , , " " ),

CREATE TABLE LegalPersons
(
 person_ID INTEGER IDENTITY NOT NULL UNIQUE, 
 person_type VARCHAR(14) NOT NULL
    CHECK (person_type IN ('Company', 'Natural Person')), 
 UNIQUE (person_type, person_ID)
);

CREATE TABLE Companies
(
 person_ID INTEGER NOT NULL UNIQUE, 
 person_type VARCHAR(14) NOT NULL
    CHECK (person_type = 'Company'), 
 FOREIGN KEY (person_type, person_ID)
    REFERENCES LegalPersons (person_type, person_ID), 
 companies_house_registered_number VARCHAR(8) NOT NULL UNIQUE
 -- other company columns and constraints here
);

CREATE TABLE NaturalPersons
(
 person_ID INTEGER NOT NULL UNIQUE, 
 person_type VARCHAR(14) NOT NULL
    CHECK (person_type = 'Natural Person'), 
 FOREIGN KEY (person_type, person_ID)
    REFERENCES LegalPersons (person_type, person_ID) 
 -- natural person columns and constraints here
);

- SQL.

. ; , , . "payroll", "EmployeesSalaries".

, , , , ( !), .

CREATE TABLE CompanyAgents
(
 company_person_ID INTEGER NOT NULL UNIQUE, 
 company_person_type VARCHAR(14) NOT NULL
    CHECK (company_person_type = 'Company'), 
 FOREIGN KEY (company_person_type, company_person_ID)
    REFERENCES LegalPersons (person_type, person_ID), 
 agent_person_ID INTEGER NOT NULL, 
 agent_person_type VARCHAR(14) NOT NULL, 
 FOREIGN KEY (agent_person_type, agent_person_ID)
    REFERENCES LegalPersons (person_type, person_ID), 
 CHECK (company_person_ID <> agent_person_ID)
);

. agent_person_ID .

 agent_person_ID INTEGER NOT NULL
    REFERENCES LegalPersons (person_ID)

. , , , , , , SQL DDL JOIN SQL DML: )

+3

" ".

, , " ". , "", " ", . , "", " ", .

, " ", "" "" (-). -spec . gen-spec . .

, , .

ENTITYALL gen-spec, . , "", , ENTITYALL. , , . , - "" "" " ", , "" " ". ENTITYALL .

gen-spec, , gen spec . , spec gen, . . tid.

+2

, , - ... - , , . , :

ENTITY

  • entity_id ( )
  • entity_type_code ( ENTITY_TYPE_CODE.entity_type_code, "" "" ..)
  • parent_entity_id ( ENTITY.entity_id, NULL)

parent_entity_id , NULL . (IE: MySQL).

OWNERSHIP - , , - . , " ". , , -.

The key to data modeling is building a model based on business rules. The model should not change often, so try to do what you can in the future - business intelligence is key to achieving this.

+1
source

More articles:


All Articles