Solved

Advise on Data structure , Many Sub Categories

Posted on 2008-10-05
7
247 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

809 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