Ofiice Cable DB design Help

[shakejuhn]

Hi all. i am trying yo create a db to store cable information. I need some help coming up with a design that will be easy to find free/spare cables. I am having a brain freeze and can't really think right now looking for some input on the best route to take.

I have multiple office building that have multile cable runs. i am looking for a way to design a db. For example i have a buliding called the "Reed bldg" inside the Reed bldg there are 5 cable runs. each cable run has 48 individual cables some in use some are not.

So far what i have come up with is a table "BLDG" that has
<pk> office name
<>address
<>site code

i have a table for each cable run called by the cable name "FO47"
<pk> cable number
<> department name
<> cubical group
<> cubical number

What i can't seem to do is put this all together in a way that makes sense.

Each group of 48 cables is also in group of 12 but some are split and some are not. for example in cable run FO47 cables 1-24 go to the copy room and cable 1-12 are connected to printers, cables 13-16 are free/spare but cables 17-24 are connected to computers.

I want to be able to query my data to tell me cables are in FO47.

Do i need a seperate table for each office and do i need to do the same for each cable? or make one table for each office and 1 table for all cables in that office? if i do it the second way what would i use as a primary key because the cable names are all the same just diffrent ports

Thanks for any input

 

[plog]

First the sweet--good job for starting at the table level. So many people start with what color the input form is going to be, or what font to use on their reports and leave the actual guts of the database (tables) as an afterthought. So good job starting in the right spot.

Now the sour, your idea of the structure is wrong. I'll give you some broad strokes, but I suggest you read up on normalization (http://en.wikipedia.org/wiki/Database_normalization) which is what the process of structuring your tables is called.

The biggest error is trying to seperate data by tables. You should instead try to seperate your data, with data. You wouldn't have a table for every cable run, you should have a table for all cable runs, and within that table a field which lets you know which cable run it is for.

From what you have described I see these tables with these fields:

Buildings
Building_ID (pk), Building_Name, Building_Address, ...

CableRuns
Run_ID (pk), ID_Building (fk), ...

Cables
Cable_ID(pk), ID_Run (fk), ...

All the ones with (pk) means its the primary key for that table, and (fk) means its a foreign key to the table above it.

Again, read up on normalization and give it a shot. Post back here with what you have, along with some sample data.

 

[shakejuhn]

Ok thanks for the input I will let you know how everything turns out