Solved

Advise on Data structure , Many Sub Categories

Posted on 2008-10-05
7
245 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I Draw a pic af what i'm trying to solve
Product-Line.jpg
0
 
LVL 1

Author Comment

by:CMDAI
Comment Utility
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
Comment Utility
0
 
LVL 1

Author Closing Comment

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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
Creating and Managing Databases with phpMyAdmin in cPanel.
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
Familiarize people with the process of utilizing SQL Server functions 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 Ac…

772 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

10 Experts available now in Live!

Get 1:1 Help Now