Search code examples
mysqldatabase-designentity-relationshipdatabase-normalizationerd

What would normalized ERD (Entity-Relationship Diagram) look like for this table

I got stuck on one project of mine. My table looks sort of like this but I think I'm just going circles and going crazy.

enter image description here

The issue is on how to "separate" attributes under the categories. Do I need to make entities for each category? Then, how would we declare keys and which table will be adopting a foreign key? Or, alternatively, there is no point in normalizing this?

I was also thinking about somehow enumerating the attributes to make categories into attributes?? Is this even a thing...

Appreciating any suggestions!

Solution

  • There's only one way that I see to normalize this table. Each line is an entity.

    Entity
------
Entity ID
Entity Letter
Entity Name
Entity Name Type

    Where Entity ID is the primary clustering key and you have a unique index on (Entity Letter, Entity Name, Entity Name Type).

    Then you have an attribute Table to hold one attribute. There's a one-to-many relationship between an entity and an attribute.

    Attribute
---------
Attribute ID
Entity ID
Category (1 or 2)
Level (x1 - x4, x1 - x8)
Attribute Value

    Where the Attribute ID is the primary clustering key, and Entity ID is the foreign key pointing back to the entity. You have a unique index on (Entity ID, Category, Level) to order the attributes.

    You can break this down further by creating a Category table and / or a Level table, but I think this is a sufficient breakdown.

    I'm not sure whether the x1 in category 2 is a typo or deliberate. Either way, it's modled.