?
Solved

Advise on Data structure , Many Sub Categories

Posted on 2008-10-05
7
Medium Priority
?
253 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:CMDAI
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
7 Comments
 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 200 total points
ID: 22647230
You are in the field of normalization!
Search for Data Normalization.
Ex: http://www.datamodel.org/NormalizationRules.html
0
 
LVL 1

Author Comment

by:CMDAI
ID: 22647399
sorry lots of typo's

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]
0
 
LVL 1

Author Comment

by:CMDAI
ID: 22647467
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
0
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
LVL 1

Author Comment

by:CMDAI
ID: 22647931
I Draw a pic af what i'm trying to solve
Product-Line.jpg
0
 
LVL 1

Author Comment

by:CMDAI
ID: 22649079
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
0
 
LVL 22

Accepted Solution

by:
dportas earned 1800 total points
ID: 22649162
0
 
LVL 1

Author Closing Comment

by:CMDAI
ID: 31503298
Thanks alot!
feel so Depressed & Happy, i should leaned this ages ago
"Super/Subtypes" ;)
0

Featured Post

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question