CMDAI
asked on
Advise on Data structure , Many Sub Categories
I've created a db, though i spent time on planning the structure,
i ended up with a lot and i mean A LOT, of progamming to do afterwords...
I want to redo the whole DB and could really use some advise.
This is a product Database:
Product
Category:Belt
Type:PK
Brand:ABC
Part No: PK123
Description
Length: 20cm
Width:2.3cm
Material:RGM
Categories: Currently have 3 but will expand to more probably 1-2 per year
Type: anywhere from 3 to 12 types per categorie
Brand: a few hundred per categorie
Part No: already at 100,000 to 1 mil per category
Now what i did is crossed my findgers and hoped i could handle the programming afterwords:
Category (ID, Category)
Brand (ID, Brand, ....)
Part No ( ID , [Category ID] , [Brand ID], [Part No])
Description_Names ( ID, [Desc Name]) * lenght, meterial , Note , Etc..
Description (ID, [Part No ID] , [Desc Name ID], Value) * 20 cm, semi Metalic, etc..
In the description i stored everithing, Sizes, Material, etc...
My biggest problem is that every Type (not category) has its own description Fields,
some are same Unique and some are same, for example all have lenght , but only 2 Types have diameter.
i ended up with a lot and i mean A LOT, of progamming to do afterwords...
I want to redo the whole DB and could really use some advise.
This is a product Database:
Product
Category:Belt
Type:PK
Brand:ABC
Part No: PK123
Description
Length: 20cm
Width:2.3cm
Material:RGM
Categories: Currently have 3 but will expand to more probably 1-2 per year
Type: anywhere from 3 to 12 types per categorie
Brand: a few hundred per categorie
Part No: already at 100,000 to 1 mil per category
Now what i did is crossed my findgers and hoped i could handle the programming afterwords:
Category (ID, Category)
Brand (ID, Brand, ....)
Part No ( ID , [Category ID] , [Brand ID], [Part No])
Description_Names ( ID, [Desc Name]) * lenght, meterial , Note , Etc..
Description (ID, [Part No ID] , [Desc Name ID], Value) * 20 cm, semi Metalic, etc..
In the description i stored everithing, Sizes, Material, etc...
My biggest problem is that every Type (not category) has its own description Fields,
some are same Unique and some are same, for example all have lenght , but only 2 Types have diameter.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Denormalized Table Overview:
|| Item || type || A______ || B_____ || C_____
|| PK1 || A ||2cm;oval || NULL || NULL
|| PK2 || B || NULL || 1cm;2cm || NULL
|| PK3 || A ||3cm; cone|| NULL || NULL
|| PK4 || C || NULL || NULL || Metalic,4pc;3cm
|| Item || type || A______ || B_____ || C_____
|| PK1 || A ||2cm;oval || NULL || NULL
|| PK2 || B || NULL || 1cm;2cm || NULL
|| PK3 || A ||3cm; cone|| NULL || NULL
|| PK4 || C || NULL || NULL || Metalic,4pc;3cm
ASKER
I Draw a pic af what i'm trying to solve
Product-Line.jpg
Product-Line.jpg
ASKER
Sorry!
Comments i posted above, "irrelevant"
I asked admin if i could delete them...
anyway here is a siplified version of wat i came to after normalization.
Main Table: ITEMS
Many smaller tables: names as types of items with one to one relationship to items table.
How to 'control' or 'make sure' that one item only has one description in one table?
(or none ofcourse)
PS* The fild name "TYPE" is a db look up of all the description table names.
Relations.jpg
Comments i posted above, "irrelevant"
I asked admin if i could delete them...
anyway here is a siplified version of wat i came to after normalization.
Main Table: ITEMS
Many smaller tables: names as types of items with one to one relationship to items table.
How to 'control' or 'make sure' that one item only has one description in one table?
(or none ofcourse)
PS* The fild name "TYPE" is a db look up of all the description table names.
Relations.jpg
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks alot!
feel so Depressed & Happy, i should leaned this ages ago
"Super/Subtypes" ;)
feel so Depressed & Happy, i should leaned this ages ago
"Super/Subtypes" ;)
ASKER
Thank's for the article,
even though i worked thorugh normalization already,
it helped me review and realize my actuall problem...
I have this data:
[Item , Type] || [Description]
[PK112 , Brake pad] || [size,width,lenght]
[PK113 , AirCon Filter] || [, Inner DiameOuter diameterter , Height, Rim Width]
Now i cant figure out how to biuld a relationship, for 'every type' has its own description table....
Table:[Items]'' Related to only one of the description table
Table:[Metalic Pad Description]
Table:[Aircon Filter Description]