6

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: ER diagram

Is there a way to achieve this or any workaround?

Thanks for your help!

2
  • How different are each of these company tables (column vise)? Could you merge it into one table "Company" with Foriegn Key on newly created table "Company Type" that would describe whether its Fuel/Engine/Maintenance
    – S4V1N
    Aug 3, 2017 at 7:08
  • Thanks for the suggestion. Unfortunately, this is not possible as a) there are quite a few company types (a dozen) and as for the columns (or attributes), while some will indeed be similar, most will need to be quite different, as they will relate to the particular category/industry they are in.
    – n1c
    Aug 3, 2017 at 16:58

3 Answers 3

2

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

1
  • 1
    I like this, otherwise you'd have to have the same company in the list twice, if it has multiple divisions fulfilling different roles.
    – BradC
    Aug 3, 2017 at 18:43
1

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

1
  • Thank you Walter! I have started researching and reading about your buzzwords, and MDCCL's answer - while for parts of it flying high above my head and requiring multiple reads for me - seems spot on. This seems to answer my question indeed. Thanks again!
    – n1c
    Aug 3, 2017 at 17:26
-1

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
1
  • Thank you codedawg82 for your contribution. I still need to read further on what Walter pointed me to - and MDCCL's insightful answer - but I think I will follow the path of the Supertype & Subtype Structure. Thanks again!
    – n1c
    Aug 3, 2017 at 17:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.