Database Design challenge: One (Table)-to-Many (Tables) Relationship?

Question

I am rather new to DBs, and after days of being stuck, researching and trying different solutions, I turn to this community's expertise for help.

I am trying to have one Person table in my MSSQL DB, that would contain all our contacts. Keeping all in one table facilitates a lot of things for us downstream. Challenge is we have to split the Company table into different tables, by categories, as we need different attributes for each - e.g. Fuel Supplier, Engine Manufacturer and Maintenance Company would each have their own table, listing companies in said category, with their own specific attributes.

Therefore, I would need a "one table to many tables" relationship where the various Company tables feed into only one attribute ("Company") in the Person table. Goal being that within a Person record, one could choose the Company that person works for - only one company, from across multiple tables.

So ideally something like that:

Is there a way to achieve this or any workaround?

Thanks for your help!

Answer 1

This seems like a case where you'd want to use inheritance (subtype/supertype), but it isn't.

A company that manufactures engines won't necessarily always be an engine manufacturer, and might not only be an engine manufacturer. It could change what it makes, or it could make multiple things

"Fuel Supplier" or "Engine Manufacturer" are roles played by companies. A company has many roles. So you have a company table, and you have a company_role table with a foreign key to company. If roles have different columns, then this would be a case where you can use inheritance

While you're at it though, you should know that it's useful to use inheritance to create a super type for individuals (people) and companies (or organizations). This would let you have say a sales order linking to the "customer" which could be a person or company

Answer 2

Your case appears to be an instance of class/subclass or, if you prefer, type/subtype. The three kinds of companies you mention are all subclasses of the generic class company. Here are the buzzwords you can use to find articles dealing with this pattern:

Class Table Inheritance; Single Table Inheritance; Shared Primary Key; Generalization Specialization; Extended Entity Relationship

A search on these buzzwords will additionally lead you to many questions and and answers on this topic both here and in StackOverflow. For one of them, click here

Answer 3

This sounds similar to an Entity-Value-Attribute EVA problem. Given your Person table, and a 1NF form of your company tables, you could create an Attribute/Category table and create relationships through an EVA table, wherein it tracks Person-Company-Attribute and the Value.

If categories are shared across companies, you create a Category (or AttributeType table.

AttributeTypeID |  AttributeTypeDesc
200               |  Gallons   

Then your EVA table may look like:

PersonID | CompanyID | AttributeTypeID | AttributeValue
1        | 100       | 200             | 300

I've known some instances where people normalize even AttributeValues though it's dependent on your use case. For example, if AttributeValues are a mix of datatypes, you'd possibly need to consider normalizing. Other perspectives I've seen are that they set everything in VARCHAR and use the AttributeTypeID to determine what the datatype should be adding a "AttributeTypeDataType" field, for example.

Your joins to reference this information might look like:

SELECT *
FROM EVA E
INNER JOIN Company C
    ON C.CompanyID = E.CompanyID
INNER JOIN Person P
    ON P.PersonID = E.PersonID
INNER JOIN AttributeTypeID A
    ON A.AttributeTypeID = E.AttributeTypeID