Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Relational Database planning

Posted on 2006-05-16
5
Medium Priority
?
232 Views
Last Modified: 2012-06-22
Hello experts,

I have got a fairly complex set of data to make sense of. I need help figuring out the best way to approach this.

The information I have is as follows;-

MAIN CATEGORIES
Lockers
Accessories
Lock Options

A locker can either be a Series I or a series M
There are many types of lockers, e.g.
Standard Lockers
School Lockers
Hospital Lockers
Golf Lockers



And each of these has its own unique type for  example;-
Standard Lockers
         - Five door
         - Four door
         - Two door

And sometimes two types of lockers have the same types of sub-types for example, School lokers and Standard lockers both have the same sub locker;
         - Five door
         - Four door
         - Two door

And That's the lockers.

Now Accessories.
Accessories are belong to both Series I or a series M
There are many types of accessories, e.g.
         - Bench
         - Stands with seat
         - Sloping Tops

Each of these accessories is available for series I lockers and Series M lockers

Series I locker accessories have different specs to series M accessories. For example;-

Locker Accessories M Series.
Stands with Seats            
To suit locker 1800h x 300w x 300d            
One locker Wide            
Two Lockers Wide            
Three Lockers Wide            
Four Lockers Wide            


I Series Accessories            
Stands with Seats            
To suit locker 1800mm(h) x 12"(w) X 12"(d)            
One locker Wide            
Two Lockers Wide            
Three Lockers Wide            
Four Lockers Wide            



I need to represent this in a relational database, any ideas guys?

Many Thanks
0
Comment
Question by:claracruz
  • 3
  • 2
5 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 16689356
one idea

One table to hold your lockers
tblLockers (LockerID, Name, Series) e.g. 1, Standard Locker, I

Lockers has types, and one type can belong to different lockers, therefore u need a locker type table and a junction table

tblLockerType (LockerTypeID, LockerType) e.g. 1, Five Doot

and to link the two, junction table

tblLockerLockerType (LockerLockerTypeID, LockerID, LockerTypeID)   - the first field LockerLockerTypeID is optional, as u can make a composite key from the other two fields



You need a Accessories table, one accesory belongs to one series, but it has different spec even though it has same name
Do Accessories belong to a locker or just a series - I wasnt too clear on that

tblAccessory (AccesoryID, Accesory, Series)



Assuming your series is fixed to I and M, u can just use that
otherwise create a lookup table

tblSeries (SeriesiD, Series)

then store SeriesID in the two tables




0
 
LVL 4

Author Comment

by:claracruz
ID: 16689447
cheers rockiroads,

In answer to your question;-


M Series Accessories .
Stands with Seats          
To suit LOCKER 1800h x 300w x 300d      <-------------------------------    
One locker Wide          
Two Lockers Wide          
Three Lockers Wide          
Four Lockers Wide          


I Series Accessories          
Stands with Seats          
To suit LOCKER 1800mm(h) x 12"(w) X 12"(d)     <-------------------------------        
One locker Wide          
Two Lockers Wide          
Three Lockers Wide          
Four Lockers Wide          


And please could you exapnd on the following line;-

You need a Accessories table, one accesory belongs to one series, but it has different spec even though it has same name

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16689477
are the accessories the same then, its jus the series has different specs?

ok

tblAccessory (AccesoryID, Accesory)

this holds One Locker Wide, Two Lockers Wide etc


next lets, create a series table, better just in case u want more series

tblSeries (SeriesID, Series)

remember, tblLocker now stores SeriesID


Next we need a tblSeriesAccessories table, this links a series with its accessories

tblSeriesAccessories (SeriesAccessoriesID, SeriesID, AccessoryID)


does that make sense?

0
 
LVL 4

Author Comment

by:claracruz
ID: 16690465
Hello rockiroads,

Is there any chance we could go over this again please...

I am completely muddled and need a lot of help to gain clarity on this.
Please note that Accessories have a main category;-

There are many types of accessories, e.g.
         - Bench
         - Stands with seat
         - Sloping Tops

and then the sub categories with these are;-

M Series Accessories .
Stands with Seats          
To suit LOCKER 1800h x 300w x 300d      <-------------------------------    
One locker Wide          
Two Lockers Wide          
Three Lockers Wide          
Four Lockers Wide          


I Series Accessories          
Stands with Seats          
To suit LOCKER 1800mm(h) x 12"(w) X 12"(d)     <-------------------------------        
One locker Wide          
Two Lockers Wide          
Three Lockers Wide          
Four Lockers Wide  

Please could you make the table structure a bit clearer, am still a bit lost.

Many thanks
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 total points
ID: 16691484
tblSeries (SeriesID, Series)

tblLockers (LockerID, Name, SeriesID)

tblLockerType (LockerTypeID, LockerType)

tblLockerLockerType (LockerLockerTypeID, LockerID, LockerTypeID)

we okay on this so far? this bit was the easy bit I guess

ok, Accessories

lets see accessory as sub categoris

if we treat accessories as independant entities, i.e. not related to lockers or series
tblAccessory (AccessoryID, Accesory)

this stores stuff like Bench, Stands with Seats etc

are the sub categories common name? in that case we can have a category table

tblCategory (CategoryID, Category)

this stores stuff like One locker wide, etc


Now what we want to do is link in to a series, a particular accessory and sub categories

so we store like this

tblXXX (god, I dont know what to call it its so long now)

contains

own primary key XXXID
SeriesID
AccessoryID
CategoryID

so u can store

1 (say 1 is primary key to M in series table)
1 (say 1 is primary key to Stands With Seats)
1 (say 1 is primary key to one locker wide)

then keep storing
1,1,1
1,1,2
1,1,3

does this make sense?




0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

578 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