Database Design Process Questions

[tfskelly]

I think this is appropriate for this group, so here goes:

I'm creating my first access database for my job. It is a
computer/software catalog where I want to keep track of about 20-30
computers and servers, the software they currently have installed on
them, when the software was installed, the specifications of the
individual computers, network information for the computers and
servers, and a catalog of all the software and licenses we have
available. I know this has been done before, but they're paying me to
do so I figure "Get paid to learn? Sure!" My question is this:

What is good practice for table layout? Should I make one large table
with ALL network/hardware/software information? Or break the tables up
as small as possible?

I'm leaning towards breaking them up into small tables because I have
many canidates for primary key for each table(ip address, hostname,
inventory#, license#, primary user). Any suggestions/tips would be
appreciated.

 

[NetworkTrade]

you don't want to repeat multiple entries of the same data...

so I think you would have a hardware table that lists each physical
server/desktop

and you would have a software table that lists each type of software

and then a third table that cross references between the two and has
specific license #

 

[Ed Warren]

Find and read several articles on data normalization.

Enlightenment will follow. ;>

(Lots of tables, few rows, no duplicate infomation, no 'calculated' values)

Ed Warren.

 

[Jeff Boyce]

It isn't so much the size of the tables as the contents. You could come up
with a (poorly-normalized) design that used small tables or one large table.

Focus first on the topic/domain. What are the entities (subjects about
which you want to save information), and how are they related? For example,
from your description, it sounds like you are working with [computers] and
with [software]. And if any of those software licenses are "site" licenses,
you could have the "same" software installed on more than one computer, so
you'd also be interested in [installed software].

You might also have a few "lookup tables" that hold the values you want
selected (rather than letting the users get creative on their data
entry/word choice/spelling.

I agree with other responders ... spend some time learning about
normalization. Here are a couple sources:

http://support.microsoft.com/?scid=kb;EN-US;209534

http://www.fmsinc.com/tpapers/genaccess/databasenorm.html

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[chris.nebinger]

I'm going to get a little more advanced than the previous posters, but
it will be good to have the right database schema.

I'd start with a Computers table. List all the things that you need to
know, including OS Version, Memory available, Serial Number, etc.

Next, have a Periphials table. This will include everything like hard
drives, monitors, optical drives, scanners, printers, etc. This is a
one to many relationship with Computers

Next is a Software table. Include fields for version information,
number of licenses owned by the company, etc.

Finally, have a ComputerSoftware table. This will be a one-many
relationship with both the Computers table, and the Software table.
This will have a record for each installation of the software on a
computer.

Finally, once you build those, look into WMI
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wmisdk/wmi/wmi_reference.asp).
This will allow you to remotely ping the computer for all the
information that you need, saving you from going out to each of the
computers. You will need WMI enabled on your pc's, and be a domain
admin to run this. The advantage is that inventories are much easier
to do, and you can get alot of information about a computer this way,
including software installed.

If you need more help, I have some classes already created that do the
brunt of the work.

Chris Nebinger

Chris DOT Nebinger AT GMail DOT com

 

[tfskelly]

I'd start with a Computers table. List all the things that you need to
know, including OS Version, Memory available, Serial Number, etc.

Next, have a Periphials table. This will include everything like hard
drives, monitors, optical drives, scanners, printers, etc. This is a
one to many relationship with Computers

Next is a Software table. Include fields for version information,
number of licenses owned by the company, etc.

Finally, have a ComputerSoftware table. This will be a one-many
relationship with both the Computers table, and the Software table.
This will have a record for each installation of the software on a
computer.

This is basically what I've got so far, minus the periphials table.
Nice to know I'm on the right track. Though I was using some table
lookups which, I've heard, are discouraged at the table level.

Finally, once you build those, look into WMI
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wmisdk/wmi/wmi_reference.asp).
This will allow you to remotely ping the computer for all the
information that you need, saving you from going out to each of the
computers. You will need WMI enabled on your pc's, and be a domain
admin to run this. The advantage is that inventories are much easier
to do, and you can get alot of information about a computer this way,
including software installed.

Wow, thats perfect. And yes, I am domain admin of this domain. I know
a lot more about the client/server side of things than the database
side. Honestly, the difficulty of the database learning curve really
caught me off guard.

Thanks for your suggestions everyone!

Kelly