A messy application, how do I "start over" to make the data work well? 3

[misscrf]

Ok, so I have one of those db's with an Inventory table. It has an auto pk, the monitor make and serial, the workstation make, model, and serial, a yes if there is a local printer, the cpu, memory, port ( which may have the port or say unable to view or hub, or dedicated switch), and an issues column.

My aim, over the summer, is to take apart this data and create an inventory application so that it can be easily updated, stored and reported on.

How do I make the tables correctly so that they are not all in one bad table? I know I need to fix this a lot! I also have a table to network printers, and will have scanners and LCD projectors, etc.

I guess I am looking for where I start, ie root table, and then where the pk's go for connecting tables with relationships and all that good stuff. Thanks for any advice!

 

[jrbarnett]

If you have never designed a relational database before, I strongly suggest that you read the document here as it explains what needs to be done in a clear and concise way.

http://www.abcdataworks.com/fundamentals.htm

The site itself is run by Tek-Tips member JeremyNYC.

John

 

[misscrf]

Thank you. I have worked with the creation of relational databases before, but I have to admit that I have never fully created an application from one. I will read this article and see how far I can get. I think that once I get the tables made the way they need to be, the forms and reports shouldn't be too bad for me to handle. I hope that when I am done I will have a great inventory application so that this school I am helping will have a great tool for keeping their inventory up to date without too much work.

If anyone has a good inventory db that I could look at please let me know. I will be looking for design ideas, once I get the table structure in place. Thanks again, John!

 

[dhookom]

There are some sample table structures at

http://www.databaseanswers.com/data_models/index.htm.

A few pertain to inventories.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[willir]

misscrf

Hint...
The problem I had with an IT database is that some aspects of a device are common and toher are not.

For example, take a workstaiton, a switch and a monitor.

They will most likely share...
- Manufacturer
- Model Number
- Serial Number
plus site specific stuff
- Location
- Asset Tag (if you use such)
- Purchase date
- Warranty info
etc.

But they will also have properties are unique from the other.

Workstation:
- Hard drive size, RAM, Video card, CD / DVD driver, burner, firmware, etc

Switch
- Number of ports, type of ports (CAT 5, Fiber SC or ST), speed of ports (10/100/1000 MB), IOS, memory, firmware

Monitor
- Size, type (CRT, plasma), max resolution (XGA, UXGA)

Now the question I asked was how the heck do I combine the common factors with the uncommon factors???

I created a very unique structure -- used a couple times elsewhere, and it works for me...

Each device has at least two tables - a master device for the common data, and a device specific table(s). This way, I can query things like the serial number, or asset tag from one table (and I dont have to check each table for this info), and I can query the device specific table for the details.

...Moving on... Here is a snippette of the design...

Design

DevMasterTbl
DevID - primary key
DevName
DevType - key to determining device type / table to use
AssetTag
Manufacturer
Model
SerialNum
LocationID - foreign key to location
DHCP - yes / no
Status
PurchaseDate
etc...

DevType is key to the functionality. Examples may include...
Server
WorkStation
Monitor
UPS

Or you can be more complicated...
PRT-Local (local printer)
PRT-Network (network printer)


DevWorkStationTbl
DeviceID - primary key
DevID - foreign key to Device Master
CPUSpeed
CPUNumber
Capactiy
NumDrives
etc...

DevSwicthTbl
DeviceID - primary key
DevID - foreign key to Device Master
SwitchType
NumPorts
etc...
(more on switches later)

I would have device tables for such things as...
- printers
- UPS's
- Servers
- Volumes (volumes belonging to a server)
- Tape drives
- Miscellaneous (catch all)
- Plus Components (catch all for major componetns such as RAID 5 drives)

Okay, I missed a couple of things.
- IP addresses
- Owner

There a couple of ways of approaching the IP address. Most often, the IP address is a one-to-one, so the immediate thought is to place the IP address on the Device Master table. But how do you handle devices with multiple IP addresses - servers, swtiches, and the big one - routers. And then some devices do not have an IP address (monitors)

I use a seperate table tied to the Devices Master...

DevIP
IPID - primary key
DevID - foreign key to the device master
IPAddress
Port (rarely used)

I guess some would wonder why not make the IP address the primary key - devices can change their IP addresses - so much easer to do this if it is not the primary key.

Then ownership
In my environment, I have shared computers - numerous people use one computer (shift work, funcational area), and some users have more than one computer (training is a good example) This means that I have a many-to-many relationship.

DevUserProfile
DevID - foreign key to device master
ContactID - foreign key to contact or owner table
ProfileDate - date profiel was last changed

Primary key is the DevID + ContactID
I could make this into a history file, who owned what computer / when, but maybe later.

Or you may have a simple office one owner / one PC. In this case just add the OwnerID or ContactID to the Device Master table.


I still am trying to improve the handling of the switches and routers. In a sense, the best approach is to tackle the switch or router has a main component and sub components - just like the DevMaster -> DevDevice table idea. And if you think about it, it makes sense to use this type of approach for workstation and servers - break it down to its components - each component - drive, dard, RAM chip as a seperate subcomponent. The problem is that this leads to a lot of complexity. For defining computers - not necessary for my needs (perhaps yours?? For a switch or router, this may be the approach - number of 10 / 100 / 1000 ports for CAT 5 and fiber, AUX port, etc.

DevSwitchTbl
DeviceID - primary key
DevID - foreign key to Device Master
SwitchType

DevPort
PortID - primary key
DeviceID - foreign key to DevSwtich
PortMedia - fiber / cat 5 / aux
PortSpeed
AutoSense
MappedDev - foreign key to device master !!!

Now, if required, I can match a specfic port to a device on the device master table. This port connects to this server, or this printer, or this switch - way, way cool! - this extra step all of a sudden becomes very useful

Enough theory ... Moving on ...

Presentation
With so many tables tied to one device, presentation to the end user (the network admins) becomes a challenge.

I use a main table - device master most commonly used info
I then use a tab subform
- tab for purchasing info
- tab for user info (M:M)
- tab for support tickets (since I track calls against a device and user)

But having 6 or 9 tabs, one for each device is a tad complex. I handle this by using the visible property. You hide / unhide forms and pages by tweaking this property. For the OnCurrent record event, I use the DevType to control which forms the user see. (And yes, you can have forms on top of each other, and only display the one relavent one).

It took me a few times going back to the basics for this desing - it is very different than the typical 1:M or M:M since it also has 1:1, and multiple tables. But it works very, very well for me.

Richard

 

[misscrf]

Willir,

Thank you very much. I read everything you talked about and I am going to work on recreating it. I do have a lot of the complexities you talk about. I have computer labs where the workstations are public. The IP's are dynamic for all workstations, public or individual. Many have laptops, and there are many hubs, ports, and dedicated switches. I have a lot of work ahead of me!
Thanks for the tutorial, and I will come back to let tek-tips know how it turns out!

 

[misscrf]

Hey, Willir. I have gone through all of the table structures, so that I can begin to plan my application. I had a couple of questions and was hoping you or anyone who sees this, might be able to explain some stuff for me. I noticed a LocationID in the DevMasterTbl, but I did not see a complete layout of the LocationTbl. I was thinking it should be:
LocationID
Room
RoomX( ie, 101A or 101B this would hold the A or the B)
RoomDevID - a number for the device in that room? I am not sure about this because I was work to make maps with visio or something to show a layout of where the computers and monitors are for such rooms as computer labs, as this is a school that has many. That is a whole other project, but I was hoping to match the maps to the inventory for easy reference, eventually.

Next!

My other question is for the switches, hubs and ports. ( Oh My! lol)
I saw layouts for 2 DevSwitchTbl's with one having a few more fields, but the other not having anything distinct from the larger one: (for easy reference, I copy them here)

DevSwicthTbl
DeviceID - primary key
DevID - foreign key to Device Master
SwitchType
NumPorts
etc...
(more on switches later)


AND

DevSwitchTbl
DeviceID - primary key
DevID - foreign key to Device Master
SwitchType


Now my primary concern is to understand how those connect to the device master, and how the port table works. I am unclear on how I connect the port table to the device table. My thought was how does the port table grab the switch and hub ( if there is a hub, may go right to switch) and device, if there is one connected to that port ( there are obviously many unused ports.)

I think that is it for now.
Thanks for all the help! I am excited to create this application!!!!!

 

[willir]

misscrf

First, the easy one. Location.
I only gave you a snippet of the stuff. Location will depend on your setup and infrastructure - a setup for campus, or intercompany will be very different than a single building.

I will leave this up to you - but be sure you have a unique ID - primary key for your location, as the foreign key on the Device Mater - this tells you where the device is located.

Simple solution would be

BuildingTbl
BuildingID - primary key
Buidling Name
etc

LocationTbl
LocationID - primary key
BuildingID - maybe
Floor - maybe
LocationArea - room number, cubicle number, functional area

For some buildings -- rooms and/or cubicle makes a lot of sense. But not all buildings support the notion of room number - you have a plant with drills or lathes - now the LocationArea would be DrillNo or LatheNo or WorkBenchNo. ...So again, it depends on your situation.

I also include a ServiceID to indicate what location service the area. (Switches, in this case)

Or it could be much more complicated. Sites, large campuses (university, large plant, etc.

...Moving on
When I designed the Device table, I did not know how much detail you needed -- and -- I wanted to use this specific device to demonstrate how you can take "normalization" down several levels....
[tt]
A -> B -> C
DeviceMaster -> (DeviceDetail)/DevSwitch -> DevComponent
[/tt]

Many would stop at A for the design level which for me personally, is too little unless you just want the basic info - make, model, tag#, s/n, price but are not concerned about the technical details.

To track and record / report on the technical details, the design has to go to level B for the general tech info, or level C for more detail.

The second rule of normalization - classifying all properties that relate to the primary key is what is included in each table - in theory.

If you are only concerned with basic technical details on the switch, then you could have various solutions, but nothing that would allow a thorough statistical report...

Solution B
DevSwicthTbl
DeviceID - primary key
DevID - foreign key to Device Master
SwitchType
Managed - Yes / No
NumPorts
... Now it gets messy ... you could have...
Solution B1, continuation from the aforementioned table,
Num1000FiberPorts
Num100FiberPorts
Num1000UTPPorts
Num100UTPPorts
Num10UTPPorts
NumAUXPorts
Comment
etc

or a more simplier solution,
Solution B2, continuation from the aforementioned table,
SwitchDesc - text box discribing port configurations

Solutin B1 is messy, and has a fairly classic symptom of a table that needs to be better normalized. However, it would still work - you could still report on number of fiber ports, copper ports, total ports.

Solution B2 is simpler - you can get the total number ports, but can not report on the number of fiber or copper ports.

...Moving on to Solution C.
This solution takes solution B1, and normalizes it further by creating a separate table for each component on the switch.

DevSwicthTbl
DeviceID - primary key
DevID - foreign key to Device Master
SwitchType
Managed - Yes / No

Solution C1
DevPortTbl
PortID - primary key
DeviceID - foreign key to DevSwtich
PortMedia - fiber / cat 5 / aux
PortSpeed
AutoSense
MappedDev - foreign key to device master !!!
IPID - foreign key to IP address table.

This is a lot more work -- you are tracking a lot more detail. The advantage, is that you can also "map" your LAN topology - which switch is connected to which device (if you wanted).

Pretend that the device has a router port - well, now you can also capture the primary IP address of the port. (For those who know -- The same IP address would be assigned to the Device Master and the DevPort tables - I know - sucks because data is captured twice, but in the real world, this is useful information)

A more simplified solution for C would be to have a summary information...

Solution C2
DevSwitchDetailTbl ' instead of DevPort
DevSwitchDetailID - primary key
DeviceID - foreign key to DevSwtich
MediaType - fiber / cat 5 / aux
mediaSpeed
AutoSense
NumPorts

This solution provides a more "normalized" view of solution B1. You can more easily track media types and number of ports, but you would not be able to "map" the LAN or WAN with this type of solution.

In Review

Device Master / Device Detail approach, you can mix and match whatever is appropriate...

For any device with one or mutiple IP addresses
DevMaster -> IP Address

For workstations
DevMaster -> DevWorkstation

For servers
DevMaster -> DevServer

For printers
DevMaster -> DevPrinter

For miscellanous devices (For example, projectors, scanners, cameras)
DevMaster -> DevMisc

For any device where you wanted to track specific componetns such as SCSI RAID drives and Controllers (with their own serial numbers).
DevMaster -> DevComponent

For Switches, Solution B1 or B2
DevMaster -> DevSwitch

For Switches, Solution C1 or C2
DevMaster -> DevSwitch -> DevPort or DevSwitchDetail


Lastly, note (again - this is important) that a Device master record can have linked records from one or more tables...

For example, for a server, with warranty components...
Devmaster -> DevServer
Devmaster -> IP Address
Devmaster -> DevComponet
Devmaster -> DevTape

But other devices, such as a server would only have one linked table
Devmaster -> DevMonitor


...Oh boy, another long post.
I hope I explained thing enough for you to understand. You will have to decide to which level you want to make things. But know, with a bit of luck, you can make an informed decision.

Richard

 

[misscrf]

well, I started making this database. I did the tables and started mapping the relationships. Then I got scared. I keep thinking I am going to mess up, or that I dont understand how the tables will really connect. What do I store in the foreign keys? How will I look everything up? I dont want to get over my head. This is a city school that cant afford a real inventory database, so I want to build it for them. If someone is willing to look at what I have started, and maybe help me out, I would really appreciate it. Thanks.

 

[MichaelRed]

misscrf,

I can only suggest that you continue the initative you have demonstrated in approaching the large scale project. It seems that several very capable practicioners are quite willing to provide both general and quite specific help, but you must respect that many (or most) of them also have professional responsabilities so may not be able to answer immediatly or with the same level of detail for each question.

While questions generally do recieve reso=ponses, most find that asking specific questions is more beneficial than the general or conceptual ones. willr is in the unique position of having generated an appwhich is almost a direct parallel to your initial inquiry, and has provided a comprehensive and well thought out beginning as well as some commentary on the vagaries of the process, thus illustrating the point re specifics vs generalities.

Other responders will probably not be as well versed in the specifics of the application, and will appreciate your taking the time and efort to express your issues clearly and succintly. There are several threads and faq's regarding maximizing the benefits of participating in the Tek-Tips. I would suggest that you read these and continue your project with the expectation of continued development of your skills through this participation.




MichaelRed
mlred@verizon.net

 

[willir]

Hmmmm

I dont understand how the tables will really connect.
What do I store in the foreign keys?

The foreign key is the same value as the primary key in the parent table. Review the following for a detail explanation...

http://www.abcdataworks.com/developers.htm

or

http://r937.com/relational.html

...and Microsoft's view...

http://support.microsoft.com/default.aspx?scid=kb;en-us;304467

Basically...
[tt]
DevMasterTbl
DevID DevType DevName AssetTag
1 serinf PDC_NYC01 S000001
2 serinf SMS_NYC01 S000002
3 serinf BDC_HOU01 S000003
4 serinf BDC_TOR02 S000004
5 serdev APP_HOU01 S000005
6 serprd APP_HOU02 S000006
7 wsprod WSNYCXX01 D000001
8 wsuser WSNYC5001 D000002
9 prtusr PRNYC1001 P000001
10 prtprd PRNYCAP01 P000002
[/tt]

[tt]
DevServerTbl
DeviceID DevID OS Config
1 1 Win 2K RAID 1
2 2 Win 2K RAID 1+5
3 3 Win 2K RAID 1
4 4 Win 2K RAID 1
5 5 Win 2K RAID 1+5
6 6 Win 2K RAID 1+5
[/tt]

[tt]
DevWorkStationTbl
DeviceID DevID OS CPU
1 7 Win 2K 2.3
2 8 Win XP 2.6
[/tt]

[tt]
DevPrinterTbl
DeviceID DevID PrtType PrtSpeed
1 9 Laser 26
2 10 Laser 45
[/tt]

Look at DevID. The master table is DevMasterTbl, and DevID is the primary key for this table. Then follow the DevID in the child tables where it is the "foreign key". This is how a record in one table is linked to a record in another table.

Now for the linkage.
The relationship should be established with the Relationship tool (from the Menu, Tools -> Relationships. Add your tables, then click and drag the primary key to the foriegn key - a popup window will open - select enforce referential integrity.)

Create a form for DevMater. In design mode, move the fields in a logical order of your choosing to the top part of the form. Leave room at the bottom of the form. From the Form's tool box, click on the "Tab Control". Place the tab control on the bottom of the form.

Now create another form to be used as a subform. For exmaple, create a form for the servers. Make it small and lean with a similar width to the main form created for the DevMaster table. Save and close this form.

Open the DevMaster main form in design mode again. Click on the subform object in the database window and drag it to the open (design mode) main form. Place the subform object in the DevMaster main in the space you left below the fields.

Now for the magic. Make sure your properties window is open (from the Menu, "View" -> "Properties"). Click on a field in the DevMaster table and then click on the subform you just placed. Look at the "Data" tab in the properties window - You should see DevID listed in the Master and Child fields. This is because you defined the relationships with the Relationship tool.

When you create a record in the subform, Access will use the DevID in the DevMaster main table as the foreign key in the child record.

Once you have done this, and verfied the data and relationships, create subforms for the other device types you want to include.

...Moving on
There are several approaches from this perspective. For example, you can create a Tab in the Tab control for each device type. This gets messy but it works. A more complicated approach would be to hide the subforms not in use.

Richard