0

I am using SQL Server database. Suppose I have an entity named 'Company' and 2 types of 'Company' are: 1) Local, 2) International. In my case there is no hierarchical relationship between Local and International Companies.

Assume that both Local and International Companies share 10 common attributes and there are 15-20 attributes specific to International Companies.

Shall I create 2 tables with 1-1 relationship between them. 1- Company (this will contain common columns, 2- InternationalComopany(this will contain CompanyId as ForeignKey and 15-20 columns specific to International Companies)

OR

Shall I use 1 table called 'Company' and put each column in that table (including 15-20 nullable columns)?

what are the things that I need to consider when I find in such a situation? does number of nullable columns make a difference in choosing one of the two options? at what level performance can get affected in both situations?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Farooq Hanif
  • 1,779
  • 1
  • 15
  • 22
  • Will the common attributes be queried regularly? If, so columns in the main table may be better. The balance would be suited in a separate attribute-company table. – Anthony Horne Jun 25 '17 at 09:32
  • create 3 tables... company master, attribute master and company attribute – RoMEoMusTDiE Jun 25 '17 at 10:05
  • Your question may have an answer here: https://stackoverflow.com/questions/13749525/relational-database-design-multiple-user-types/13752304#13752304 – Walter Mitty Jun 26 '17 at 12:51
  • Possible duplicate of [Relational database design multiple user types](https://stackoverflow.com/questions/13749525/relational-database-design-multiple-user-types) – philipxy Jul 01 '17 at 16:19

1 Answers1

1

The primary thing you want to consider are foreign key relationships. Will other tables be referring to a "company"? Or, will other tables be referring to a "local company" and to an "international company"?

If the relationships are to all companies, then you want a single table. This allows you to enforce data integrity relationships.

If the relationships are always to one or the other type, then you can put them in two different tables. They would appear to be two different entities.

If sometimes the relationships are one type, sometimes the other, then you would usually want all the values in a single table. There are other ways to handle the "type-specific" relationships.

Whether or not you store the other attributes in the same table is the next question. There is little harm in doing so -- you can readily create views for one type of company or the other.

When would you put the additional columns for international companies in another table? Here are some criteria:

  • The size of the columns. If all the columns do take up space on the data page. You have to decide if you want to use this space for all companies. This depends on the number and type of columns.
  • The relative numbers of local versus international companies. If most companies are international, then those additional columns are generally being used to hold values.
  • How the columns get used. If most queries are usually using only the columns for local companies, then reading in the additional columns is unnecessary. This is akin to "vertical partitioning" -- storing less frequently used columns in separate tables.
  • Do the columns have different access requirements? It is easier to grant access to tables than columns within a table.
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786