CODEWITHSUNDEEP
sqlsql-serverdatabase-normalization
JBond
JBond

Reputation: 3222

SQL - Database normalisation

I have the proposed table structure to extend a database:

Proposed table structure

Within the OrganisationalAssets table. The following columns are nullable:

The problem we've come across is that the Organisation_Id column is only nullable for certain types of Assets. Some assets relate to an Organisation and some never will. For the ones that do relate to an Organisation, the other columns, Location and Language are applicable. If Organisation is NULL. Then Language and Location are redundant columns for those assets.

To me, this sounds like I have a table that is incorrectly storing the two types of assets. Internal and External assets. Internal are not constrained by Organisation, Language and Location, whilst External assets are.

However, I'm not entirely sure how to change the structure to reflect this. Regardless of whether they are Internal or External assets, they can still both have many AssetSettings associated with them. The external assets will have a different set of AssetSettings dependent upon there Organisation, Location_Id and Language_Id whereas an internal asset won't. They will only ever have one collection of settings each.

I'm not sure whether to:

  1. Just leave the structure as it is because it works. Although it isn't normalised correctly.
  2. Try and restructure the tables to allow for both types of assets and if so, how.

Sample data Here is some sample data to hopefully make a bit more sense of what I'm asking:

OrganisationAssets

+----+----------+-----------------+-------------+-------------+
| Id | Asset_Id | Organisation_Id | Location_Id | Language_Id |
+----+----------+-----------------+-------------+-------------+
|  1 |        1 | 2               | 3           | 4           |
|  2 |        1 | 2               | 3           | 999         |
|  3 |        2 | NULL            | NULL        | NULL        |
+----+----------+-----------------+-------------+-------------+

The first two rows are for AssetId 1. It belongs to two different locations and is an External asset. The third row is an Internal asset therefore the other columns are redundant.

AssetSettings

+----+------------------------+------+--------+
| Id | OrganisationalAsset_Id | Key  | Value  |
+----+------------------------+------+--------+
|  1 |                      1 | Key1 | Value1 |
|  2 |                      1 | Key1 | Value1 |
|  3 |                      2 | KeyA | ValueA |
|  4 |                      3 | KeyB | ValueB |
+----+------------------------+------+--------+

In the AssetSettings table. We can see two lots of settings for the Asset with Id1 (i.e. OrganisationalAsset_Id 1 and 2). We can also see the second asset appearing in the table and it can only ever have one group of settings as it does not belong to any organisation, location or language.

Upvotes: 0

Views: 123

Answers (2)

T I
T I

Reputation: 9933

1) This seems reasonable, it's a choice between redundancy and simplicity, understanding the data, usage etc. will help youu in this decision.

2) As you've identified two distinct kinds of Assets why not model it as such

Assets (Id, Name, AssetType_Id) 
    -- Id is PK

AssetTypes (Id, Name)
    -- Id is PK

InternalAssets (Id, AssetSettings_Id) 
    -- Id is PK and FK referencing Assets(Id)
    -- AssetSettings_Id is FK referencing AssetSettings

ExternalAssets (Id, Asset_Id, AssetSettings_Id, Organisation_Id, Location_Id, Language_Id)
    -- Id is PK 
    -- Asset_Id is FK referencing Assets(Id)
    -- AssetSettings_Id is FK referencing AssetSettings

AssetSettings (Id, Setting1, Setting2, Setting3)
    -- Id is PK

You may want to add a constraint such that an InternalAsset cannot be an ExternalAsset and vice versa.

You could also create a view of OrganisationalAssets which would union the two *Assets tables which may aid to simplify querying.

Upvotes: 0

GSazheniuk
GSazheniuk

Reputation: 1384

Well, as I can see from the structure you have described the best solution to normalize your data is to move all of those 3 fields (Organisation_Id, Location_Id, Language_Id) to [AssetSettings] table. In this case you would avoid having NULL values for inappropriate assets and you always can refer to required field wherever and whenever you need to.

And you can totally remove OrganisationalAsset table and link AssetSettings table straight to Assets table.

And you still have all the assets in one table, so you do not have to split them in two tables, in which the most common issue to arise is having two ID fields for assets, so you would need to keep additional AssetType field for every table that contains AssetId.

Upvotes: 1

Related Questions