Search code examples
sqlattributesentity-attribute-value

Is it bad practice to create a SQL table knowing full well there will be no data for some columns (for certain rows)?

I am trying to avoid the EAV model as I would like attributes to have the appropriate type. However I want to decide if I should create multiple attribute tables for different types of products or just one bit one.

All the items are "products" so I feel they belong in one table. However some product types don't share the same attributes as others. Consider the following:

Table: Product
- productID [INT]
- name [VARCHAR]
- type [FK]

Table: Product_Type
- productTypeID [INT]
- description [VARCHAR]

There are three types of product: Main, Accessory and Supply. The different types of products share many attributes but not all. So I am wondering do I create one attribute table with all the attributes with the understanding that accessories/supplies will not be using half the columns or create separate attribute tables for each type of product.

OPTION #1: One Attribute Table

Table: Product_Attribute
- productID [FK]
- nameInternalCode [VARCHAR]
- nameExternalCode [VARCHAR]
- dateAnnouce [DATE]
- dateSell [DATE]
- serviceable [BIT]
- etc. etc. etc.

PROS: The front end logic (PHP) is simple because you only ever need to select the product attributes from one table.
CONS: While all items are "products" they don't all have the same attributes. So rows for accessories or supplies for example will have columns that will NEVER have data.

Is this practice considered to be bad?? To create a table knowing full well that some rows will never contain some columns worth of data?

OPTION #2: Two/Three Attribute Tables

Table: Product_Main_Attribute
- productID [FK]
- nameInternalCode [VARCHAR]
- nameExternalCode [VARCHAR]
- dateAnnouce [DATE]
- dateSell [DATE]
- serviceable [BIT]
- etc. etc. etc.

Table: Product_Accessory_Attribute
- productID [FK]
- dateAnnouce [DATE]
- dateSell [DATE]
- serviceable [BIT]
- etc. (Shorter List)

Table: Product_Supply_Attribute
- productID [FK]
- serviceable [BIT]
- etc. (Shorter List)

PROS: The table better represents the type of data it is responsible for. There are no longer any columns which will have no data.
CONS: Creates additional logic requirements on the front end (PHP). Will have to first determine what type of product it is to know which table to read attributes from.

Solution

  • I design my Databases based off how they are likely to be used.

    Read Heavy Databases - tend to more denormalised.

    Load Heavy Databases - Tend to more normalised pretty hightly

    It is a matter of the right tool for the job.

    I would personally go with the second option. out of the ones presented but i would break it down even more. 

    **Products**
-ProductID
-ProductName

**Product_Main_Attribute**
-Surragate Key
- ProductUD
- nameInternalCode 
- nameExternalCode
- dateAnnouce 

**Product_Sale**
-Surragate Key
-ProductID
-DateSell

**Product_Service**
-Surragate Key
-ProductID
-Serviceable

    This will allow you to write to the database a lot faster. The other thing is that this will also help reduce the risk of duplicate data appearing in the database.

    I work in a system where our tables are in the 100's in a similar design structure and due to the design there is actually a lot less data due to the way everything is referenced.

    To quickly answer your question. No not really