Solved

Advise on Data structure , Many Sub Categories

Posted on 2008-10-05
7
250 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 50 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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 450 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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

732 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