-1

In large system, when analyzing the database, there are about 50 different categories in the requirements, which should represented as tables.

Each category has many attributes - columns-, all these categories has the same of 50% of columns. For example, each category has (id, name, date, state, admin, dept), all categories have those attributes, but each category has its own attributes which differ from each other, they are about 3 - 5 attributes.

Now, how to represent them in the physical database as tables? One table, or table for each category, what about redundancy?

Dale K
  • 25,246
  • 15
  • 42
  • 71
alnajm
  • 335
  • 1
  • 2
  • 14
  • Does this answer your question? [What is the best way to implement Polymorphic Association in SQL Server?](https://stackoverflow.com/questions/7000283/what-is-the-best-way-to-implement-polymorphic-association-in-sql-server) – Charlieface Nov 24 '21 at 09:52

2 Answers2

3

Depends on what exactly you are trying to achieve.

If your primary concern is disk space, I would recommend to consider sparse columns, with column sets as an option, if necessary. In this scenario, you can put all these entities into a single physical table, with mandatory attributes being normal columns and specific attributes being declared as sparse.

If you are thinking about a normalised model which would eliminate most of data anomalies, a typical solution is a supertype-subtype hierarchy. The main table stores only the attributes that are mandatory for all entities, and child tables contain only main table' identifier and attributes specific to this particular category. All the child tables reference the "supertype" table via foreign keys.

Sometimes, depending on subject area, a more complex model with additional "nesting" levels might be employed. You can think of this as a class inheritance hierarchy - the analogy is very close, actually.

Of course, both (and other) approaches have their strengths and weaknesses, so you might need to read up on the subjects and make a choice.

Roger Wolf
  • 7,307
  • 2
  • 24
  • 33
-1

As some attributes are only applicable to some categories, you can think of Entity-Attribute-Value model, for storing the categories.

There are multiple ways of representing EAV models in a database. You can refer to below article: https://inviqa.com/blog/understanding-eav-data-model-and-when-use-it

The EAV model way of data storage comes up with its own challenges, when you query the database. So, see whether it will suit your needs, before choosing the same.

Venkataraman R
  • 12,181
  • 2
  • 31
  • 58