Database model / design best practice

database-designdimensional-modelingrelations

I'm looking for a some guidance/advice on how to best model my data.

Some background: I have location information that I'm trying to model. There are two categories of locations (company and non-company locations), as such, not all locations have the same data elements.

The two tables below represent my current model (without enumerating all columns).

My thoughts on the current design:

  1. If I combine the two tables below I will have to put in null values for company_code, because not all locations have a company_code. I do not want to add nulls values because of the huge number of different data elements between the two different types of locations.
  2. The location_company and location are two important concepts in the company. I would like to have tables in the database two represent these two concepts.

enter image description here

I'm unclear if this is the best approach. Any thoughts on the design would be greatly appreciated. Any additional thoughts are also welcomed.

If my issue is unclear, please ask. I will graciously add clarification. Thanks in advance.

Best Answer

I would say that if the company locations and non-company locations store a fair amount of different data, then to avoid a bunch of NULLS I would have two tables, and let the application insert data into the appropriate table. A different approach could be to have a table [Locations], with all the data that it needs, then have a table [CompanyLocations] and another table [Non-CompanyLocations]. The tables for company and non-company locations would then reference a base table using a foreign key, something very like what you have in the image, if I am reading it the same way you are.