CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
Grant W
Grant W

Reputation: 27

Is a generic ID column in a SQL table a bad idea?

In our database we have many tables with a 'Notes' column. This is important functionality, but for most rows the value of Notes is null. These tables have many columns and we would like to remove some columns for better legibility.

We could add one Notes table for every table that has a notes column. But this would create clutter of a different kind- too many small tables.

My idea is to create a generic Notes table and also a reference table. The Notes table would have a column for the notes text, a column for the id of the row being linked to, and a foreign key to the reference table. The reference table would have a text value for each table for which we need notes. Using these two tables we should be able to link the note back to whichever table and column it came from.

By using this solution, we remove any cases of null values from notes and also slim down some of our tables. All at the modest price of two additional tables. It feels very 'hacky' to me however. Is there a reason why using a 'generic' id column or a reference table of other tables is a bad idea from a DB management perspective?

Upvotes: 1

Views: 895

Answers (3)

Uwe Gorek
Uwe Gorek

Reputation: 1

Additionaly for SQL Server you can use sparse for the note columns to reduce size. But i used a similary approach myself. (Note column needed for many columns to write info / changerequest / lockcomment. But normally never used). Works fine and can be programmed genericaly in source. But if you need only one comment column per table i wood prefer sparse

Upvotes: 0

Vishal Raja
Vishal Raja

Reputation: 313

IMO, the other solution of managing two tables doesn't bring in much efficiency but adds complexity to the solution. You should probably stick with the the notes column in the original table with datatype as varchar.

Generic id column is not bad inherently but the use of it generally gives smell of bad/hacky design.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Managing the references to disparate entities can be really challenging in SQL Server. Postgres, by contrast, supports inheritance which makes this much simpler.

So, my recommendation is to add a notes column to every entity where you want notes. You an add a view to bring all the notes together if you need a view of all the notes.

This has minimal impact on performance or data size. There is no additional overhead for a varchar column, other than the additional NULL bit -- and that is pretty minimal.

Upvotes: 4

Related Questions