CODEWITHSUNDEEP
database-designrelational-database
James
James

Reputation: 3184

How should I model the database schema to accommodate many dependent columns with flexibility to easily add new ones?

I have a relational database where I'm storing Seller information in a table called (naturally enough) Seller. In that table, I have a column called industry. I also have many attributes about the Seller. The attributes applicable to a Seller depend on its industry. On average, an industry will only four attributes that are applicable. The max attributes per industry has been 12 attributes (so far).

Currently, I'm representing each attribute as a column in the Seller table. I have 21 attributes and thus have 21 corresponding columns allowing null on the Seller table.

The application is fairly new (3 months), and the business has requested 6 new attributes in this time (for a total of 27 attributes). I'm guessing that more attributes will continue to be requested in the future. I'm now questioning the choice to have each attribute as a column in Seller. Is there a better way to model this in the database?

I had someone mention having a single column called attribute. That column would be of type XML. The XML that would contain the attribute names (relevant to the industry) with their corresponding values. Is that a good approach?

Edit There are 154 industries. So, many industries have attributes in common.

Upvotes: 1

Views: 158

Answers (1)

Nir Alfasi
Nir Alfasi

Reputation: 53525

If the attribute is an industry attr. it belongs in the industry table, otherwise, it belongs to the seller column. If it's "per vendor" but "industry dependent" you should create more tables for that: seller_industry_attributes (one table per industry).

For example, if you have the following industries:

  • education
  • web

then you should have the following tables:

  • education_attributes
  • web_attributes

education_attributes table, for example, should contain the columns: seller_id and one column per attribute, so if, for that same example, we have the attributes:

  • number of employees
  • subject of study
  • etc

then education_attributes table should have the columns:

  • seller_id
  • number_of_employees
  • subject

Upvotes: 1

Related Questions