0

These are my requirements:

  1. Each Category has Multiple Groups
  2. Each group has multiple Attributes
  3. Each attribute has multiple values

So far i have come up with this DB Design

CategoryGroups Table

GroupID | CatID[References Category(CatID)] | GroupName

CategoryAttributes Table

AttributeID | AttributeName | GroupID[References CategoryGroups(GroupID)] | AttributeValue

So do you guys think this is a neat design?? Any suggestions??

Monodeep
  • 1,392
  • 1
  • 17
  • 39

2 Answers2

1

Apart from naming your second table GroupAttributes, because it doesn't know anything about Category, this is one, most used, good approach. You have multiple 1:N (one to many) relationships, and you're referencing tables in the right way.

Máthé Endre-Botond
  • 4,826
  • 2
  • 29
  • 48
  • My main concern now is the how am i going to upload the data in batches?? What i am thinking is that the user has to select the category 1st then the group and then he uploads the attributes with values...am i right? – Monodeep Sep 28 '12 at 06:54
  • Inserting batch data can be somewhat complicated. I mean the GUI, the user must use. If you mean batch value for a single group, then sure, this workflow can be easily imagined both as GUI and code. But when you want to allow adding values for multiple categories and groups, the GUI will get really complicated and hard to see trough. If there's nothing like a user interface, than it should be pretty easy to use, just as you said. You can also search Category or Group by name to get ID and insert data as high in the hierarchy as you need. – Máthé Endre-Botond Sep 28 '12 at 07:02
  • Ya am going to do it as you said..inserting by Groups..one more query...any idea how am i going to show the attributes grouped by group name in the GUI?? Say i have 3 groups ...and 5 attributes under each group..how am i going to show this in the GUI??Under Group 1>>>5 Attributes...Under Group 2>>>>>>>5 Attributes and so on... – Monodeep Sep 28 '12 at 07:17
  • There are many ways to do it, depending on how many info you're willing to show. For example, for comma separated values, you can select from database groupped by group, [something like this](http://stackoverflow.com/questions/149772/). For detailed display, you'll have to query the db for as many times as the number of groups. You could also create a view, to merge all info, for easier search. – Máthé Endre-Botond Sep 28 '12 at 07:57
0

Is there any reason not to have four tables for your four concepts?:

Category: id, <category stuff>
Group: id, category_id, <group stuff>
Attribute: id, group_id, <attribute stuff>
Value: id, attribute_id, <value stuff>

Primary keys are the id columns.

Foreign keys are as named (i.e. attribute_id is a foreign key to Attribute.id)

emperorz
  • 429
  • 3
  • 9