Solved

Advise on Data structure , Many Sub Categories

Posted on 2008-10-05
7
246 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
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Read about achieving the basic levels of HRIS security in the workplace.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now