CODEWITHSUNDEEP
sqlnormalization
Neophear
Neophear

Reputation: 542

Normalization of SQL tables

I am creating some tables for a project and just realized that many of the tables have the same structure (Id, Name), but are used for different things. How far should I go with normalization? Should I build them all into one table or keep them apart for better understanding? How does it affect performence?

Example 1:

TableObjectType (used for types of objects in the log)

Id  Name
1   User
2   MobileDevice
3   SIMcard

TableAction (used for types of actions in a log)

Id  Name
1   Create
2   Edit
3   Delete

TableStatus (used for a status a device can have)

Id  Name
1   Stock
2   Lost
3   Repair
4   Locked

Example 2:

TableConstants

Id  Name
1   User
2   MobileDevice
3   SIMcard
4   Create
5   Edit
6   Delete
7   Stock
8   Lost
9   Repair
10  Locked

Ignore the naming, as my tables have other names, but I am using these for clarification.

The downside for using one table for all constants is that if I want to add more later on, they dont really come in "groups", but on the other hand in SQL I should never rely on a specific order when I use the data.

Upvotes: 0

Views: 189

Answers (2)

Neville Kuyt
Neville Kuyt

Reputation: 29619

Just because a table has a similar structure to another doesn't mean it stores the data describing identical entities.

There are some obvious reasons not to go with example 2.

Firstly, you may want to limit the values in your ObjectTypeID column to values that are valid object types. The obvious way to do this is to create a foreign key relationship to the ObjectType table. Creating a similar check on TableConstants would be much harder (in most database engines, you can't use the foreign key restraint in this way).

Secondly, it makes the database self describing - someone who is inspecting the schema will understand that "object type" is a meaningful concept in your business domain. This is important for long-lived applications, or applications with large development teams.

Thirdly, you often get specific business logic with those references - for instance, "status" often requires some logic to say "you can't modify a record in status LOCKED". This business logic often requires storing additional data attributes - that's not really possible with a "Constants" table.

Fourthly - "constants" have to be managed. If you have a large schema, very quickly people start to re-use constants to reflect slightly different concepts. Your "create" constant might get applied to a table storing business requests as well as your log events. This becomes almost unintelligible - and if the business decides log events don't refer to "create" but "write", your business transactions all start to look wrong.

What you could do is to use an ENUM (many database engines support this) to model attributes that don't have much logic beyond storing a name. This removes risks 1, 2 and 4, but does mean your logic is encoded in the database schema - adding a new object type is a schema change, not a data insertion.

Upvotes: 1

etsa
etsa

Reputation: 5060

I think that generally it is better to keep tables apart (it helps documentation too). In some particular cases (your is the choice...) you could "merge" all similar tables into one (of course adding other columns, as TAB_TYPE to distinct them): this could give you some advantage in developing apps and reducing the overall number of tables (it this is a problem for you).
If they are all relatively small table (with not many records), you should have not performance problems.

Upvotes: 1

Related Questions