0

I have a case where mutually exclusive sub-types need to share common fields.

Here is my design as of now.

Tax Entities

Here are the mutually exclusive sub-types:

Tax_Entity_Type_Code  Tax_Entity_Type_Desc
--------------------  ------------------------
A                     Agency
C                     Company
I                     Individual Taxpayer
P                     Individual Non-Taxpayer

This is a similar approach to how AdventureWorks was designed.

Is my design fine or am I violating a database normalization rule that will lead to trouble?

Stick-With-SQL
  • 213
  • 1
  • 2
  • 6
  • https://dba.stackexchange.com/questions/36573/how-to-model-inheritance-of-two-tables-mysql – Janez Kuhar Oct 09 '17 at 21:53
  • Thanks. Its interesting because that post doesn't use an Entity-Type to show what the type represents. I wonder why. – Stick-With-SQL Oct 09 '17 at 22:01
  • Hi. Why do you use the term "normalization"? What do you think it means? Design is complex. "Fine" means nothing unless you tell us what you mean by it. Re the dba link, there are many design/modeling methods. You just know yours. What is it? What do you mean, "use an Entity-Type to show what the type represents"? An entity is of a certain type/kind when it qualifies to be in a given table. Type tags/discriminants are typically redundant and are one way to approach constraint enforcement given the limitations of SQL. PS There are many questions & answers re database/SQL subtypes. Google. – philipxy Oct 10 '17 at 03:30
  • Relational Modeling aside... I'm hoping that "Tax_Entity_ID..." doesn't translate into social security number... Because that is the very last thing you want spread throughout your database. If you must collect it, it should be kept away from any other PII and encrypted. Best practice is to not collect it all. – Jason A. Long Oct 10 '17 at 04:01
  • 2
    Not quite "fine". See https://stackoverflow.com/a/4970646/562459 – Mike Sherrill 'Cat Recall' Oct 10 '17 at 11:14
  • Your design looks good to me. See [tag:class-table-inheritance] and [tag:shared-primary-key] for other Q& A on the same topic. Normalization has little to do with representing inheritance relationships in tables. – Walter Mitty Oct 10 '17 at 11:25

0 Answers0