-3

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!

philipxy
  • 14,867
  • 6
  • 39
  • 83
Gin
  • 1
  • 1
  • Ask 1 specific researched non-duplicate question, about the 1st place you are stuck following a published reference. State what your goal is & what you are starting with. [ask] [help] PS Please use text, not images/links, for text--including tables & ERDs. Paraphrase or quote from other text. Give just what you need & relate it to your problem. Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. PS That includes, tell us how to read that table. PS "any suggestions" is not a valid question – philipxy Mar 18 '21 at 14:17
  • Hello @philipxy, thank you for your advice! It is my first time publishing a question, and it was easier for me to use images at describe the problem and reduce language barrier. I see now that I could do better describing the issue. Either way, I'll make sure to be more concise and precise going further. Thanks a lot! – Gin Mar 19 '21 at 03:55
  • Questions are forever, the intent is to have quality questions & answers for others in the future, please consider improving this. Especially when it's not clear what the 1 question is, or what any of the questions you ask mean in detail, and when if clear they would be closable as duplicates and/or as asking for tutorials/textbooks to be (re)written. (But please don't edit in a way that invalidates reasonable answers.) [meta] [meta.se] PS ["Normalize" to a "1NF" has no particular meaning.](https://stackoverflow.com/a/40640962/3404097) Give yours. – philipxy Mar 19 '21 at 04:22
  • @philipxy I understand, now that you've explained the idea behind those requirements. Indeed, I should have kept in mind the big picture of stack. Regarding the issue, I actually resolved it on my own. Would you rather suggest I delete the question entirely or modify it including the solution? – Gin Mar 19 '21 at 05:19
  • Poorly received posts (score <=0) count towards question bans whether deleted or not. Why not try to improve? Maybe you will get upvotes and/or undownvotes. Also although one can delete a question while it has just 1 answer with score <=0 it is generally frowned upon since somebody went to the effort of answering. PS The question in your comment is the sort that is addressed in the [help] & that has been asked at the meta links. Learn about googling with 'site:' & about SO/SE text-based searching per the search box help. – philipxy Mar 19 '21 at 05:27

1 Answers1

0

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.

Gilbert Le Blanc
  • 50,182
  • 6
  • 67
  • 111