Database Headaches - Mind not working today

I cant seem to get my head around how to create this

Each Bold Letter is a Database Table

I need this to work with Entity Framework

Product

[ Product belongs to one group]

Product Group - [Computer]

[many to many]

[Group has many items]

[Product belongs to one Group Item]

Product Group Item - [Hard Drive]

[many to many]

[Group Items has Many Fields]

[Fields does not change 开发者_运维百科for each product only changes for each Group Item]

Product Group Item Field - [Form Factor]

[Group Item Fields has many values]

[Field Values Change with each product]

Product Group Item Field Values - [ 3.5" ]

I can pretty much get the first 3 to work

my problem is how to do the last two tables

I hope I explained it clear enough

thanks in advance

alt text http://myimgs.net/images/cjgo.gif

maybe this will help or just hurt who knows

Product = is a harddrive

so:

Group - Computer

GroupItem - Harddrive

GroupItemField - Form Factor : GroupItemFieldValue - 3.5"

GroupItemField - Capacity : GroupItemFieldValue - 600MB

etc...

but the field value changes for each product of type Harddrive but the field does not

I think you may be trying to over-generalise your solution.

It seems to me you want to standardise the information you capture for different kinds of products.

E.g. Hard Drives

1  Supplier1   Model 1a   3.5"    600MB
2  Supplier1   Model 1b   3.5"    200GB
3  Supplier2   Model X    2.5"    600MB

And you want to represent the attributes in a single table:

1 FormFactor   3.5"
1 Capacity     600MB
2 FormFactor   3.5"
2 Capacity     200GB
3 FormFactor   2.5"
3 Capacity     600MB

The problem is that over-generalising like this you lose all the data integrity controls that your RDBMS provides.

You may be better off with:

Product (*Id, Name, GroupId, Supplier, Model, ...)
HardDrive (*Id, FormFactor, Capacity, ...)
Monitor (*Id, Resolution, ...)
Memory (*Id, Capacity, Speed, ...)

Each of the above product specific tables has an optional-to-one reference to Product. With such a design, it becomes impossible to capture Monitor attributes for a hard-drive unless you add a Monitor row for the product.

That said, if you're willing to forego integrity controls, or manage them yourself in code, then looking at sample data helps to produce your schema. (I'm going to use the terminology of attributes.)

AttributeValues (*ProductId, *AttributeId, Value) -- Note a problem here: what type should Value be?

You will need some way of indicating what attributes are allowed for each Group:

HardDrive FormFactor  Req
HardDrive Capacity    Req
Monitor   Resolution  Req
Monitor   Colour      Opt
Memory    Capacity    Req
Memory    Speed       Req

GroupAttributes (*GroupId, *AttributeId, IsOptional)

Then you need to indicate the group to which a product belongs (so that you can figure out which values need to be filled in)

1  Supplier1   Model 1a   HardDrive
2  Supplier1   Model 1b   HardDrive
3  Supplier2   Model X    HardDrive
4  Supplier2   Model M1   Monitor

Products (*ProductId, Group, SupplierId, ModelNo)

I'm not sure where your GroupItems fit in.

Relationships
Products.GroupId -> Groups.GroupId
Products.SupplierId -> Suppliers.SupplierId
GroupAttribute.GroupId -> Groups.GroupId
GroupAttribute.AttributeId -> Groups.AttributeId
AttributeValue.ProductId -> Products.ProductId
AttributeValue.AttributeId -> Attributes.AttributeId

NOTE
I've illustrated how you can add columns defining rules for the attribute values. You could do the same for the Attributes table where you'd probably at a minimum need to indicate the data-type of the attribute.

You may notice that it won't be long and you'll soon be replicating the meta-data that your RDBMS provides to define tables and columns. The highly generalised solution does have its benefits such as using a simple template mechanism to capture and view products. But it becomes quite a bit more difficult (in code and processing time) to perform other tasks. So I suggest you consider your requirements holistically against the design.