Im creating a db to keep records of computers that have been assigned to persons in their various departments and also unassigned computers and spare parts.


problem:


For the machine specifications table should i put in the following fields as columns

Hard Drive 1
Hard Drive 2
Hard Drive 3
Optical Drive 1
Drive 2
Drive 3

or should i just create a field called Component and another field called type/size e.g. component would be hard drive or opticqal drive and Type/Size would be 60GB or DVD+RW

or should i just create individual tables to store the Hard drive info separate from the optical disk and separate from the memory.


The database is going to get really big which is why i wana structure it properly you r help would be really appreciated.

You have a many-many relationship between computers and components. You should have a component table that describes the components you want to track along with their specifications. Then in the relation table, you would have the id of the PC, the id of the component, the serial number of the installed component, and possibly the date installed.

This will make for more rows, but in the end you will be storing less data since you won't have to allocate space for unused components in the PC record.

A fourth table will help your reporting. This table would be a category table so that you would be able to produce reports that reflect ony certain types of components.

Strictly FYI 'cause Pat already told you good advice:

Hard Drive 1
Hard Drive 2
Hard Drive 3

Whenever you see fields named XXX1, XXX2, XXX3, XXX-ad-nauseam, you have what is called a "repeating group" - which violates 1st normal form. Look up normalization to see why - and to see why violating it is a bad thing.

In my case, which was also a computer tracking DB, I had several tables.

1. The computer description including a field linking to our employes list to show who was responsible for it. (Which means we had a separate "person" table for various purposes.) Plus location, in case it wasn't at that employee's desk, which sometimes it wasn't.

2. Disk table - linking many to one with the computer on which the disks are used as "locals" - and if the disks are separately accessed remotely, that doesn't count for any other computers... but the disk table has a Yes/No that shows that the disk COULD be accessed via networking.

3. Other Components table - linking many to one with the computer in which the components resided.

Special wrinkle: Some of our machines are clusters. And not all of them that are clusters are Windows clusters. In that case, we name the cluster as a computer and the CPU as an Other Component. When the CPU has local disks not directly shared by the cluster, we have to do double-entry bookkeeping slightly, 'cause the cluster members have local disks and are also part of a "higher level" system. Takes a couple of extra flags to note that a particular entry is stand-alone, is a cluster, or is PART of a cluster.

Special wrinkle 2: When we use a Storage Area Networking host, even though it NEVER EVER runs applications, we name the box according to its maker and "local" serial number so that it can "own" its own disks.

Special wrinkle 3: We also needed a special "Alias" table for naming things because of cluster aliases and "special service" aliases hosted by a machine that has its own name, but has a secondary name for a given service.

Special wrinkle 4: Our linking table defined who was the admin, who was the security officer, who was the data approval authority, who was the contract manager for that machine's project, etc. - a case where a machine table entry has multiple relationships to the same (personnel) table, but for different fields: The SysAdmin, DAA, CtrMgr, SysSec, etc... and they link through different instances of the personnel table in the relationships block.

Of course, since this is a U.S.Navy hosting site, we are talking about over 1000 systems total (can't tell you the exact number, I lost track when I handed it off to someone else), ranging from single-CPU Pentium P3s to full blown Alpha clusters with 4 CPU per box. Plus blade servers, RISC systems, ... you name it, we've probably got a couple of them somewhere.

I won't even TRY to tell you what the applications, security manager, and user base tables look like. But this is to show you how far this project can go when you grow.

Thanx for the reply, could you just be a little more specific about the components table and the relations table. theat kinda hazy in my mind

Ok I understand what you're saying about the normalization thing just now. The thing is i wana get as best a setup for this thing. By the way there are some guys here that dont think access is worth anything which is why i really wana do a good job at the structural level of this assignment.

Most of our components were either storage devices or printers tied to the machines or were network cards with specific addresses. We eventually broke that apart.

So we had a components table that had a foreign key to the computer "owning" the devices, plus the device "type-name" ie printer, external tape, internal tape, etc, plus Maker, Model, Serial for each such device.

The NIC table held the card's maker, model, s/n, plus its MAC and IP addresses. Plus the FK to the computer table to show the box in which it was installed. Plus the node name and domain name associated with that IP address. We had to split this 'cause as our machines got more advanced, they started holding multiple network cards, each with different IP, MAC, and network name. Sometimes different domains.

So the whole thing tied together through autonumbers on the computer table as prime keys, with the other tables having the same autonumber as a foreign key, with a whole BUNCH of many-to-one relationships.

The bit about "who was responsible for what" is 'cause at our military site, everyone has a small piece of the pie. So we had pointers to our "significant persons" list - as part of the machine table - to show that box "WHOSIWHATSIS.CONFUSER.NAVY.MIL" had "Jake the Snake" as SysAdmin (FK to person table), "Miss Molly Golly" as Data Administration Authority (FK; she controlled who had rights to access the data), "Joe Schmoe" as ContractContact (FK), and etc etc etc.

To make this work, I put the person table in the relationship window as many times as I had "special" pointer fields - in my case, five. Then I established the relationship between persons and the machines they touched, always in a particular order of assignment, using one of the relationships per pointer. The five entries of the person table were tagged as no tag, "(2)", "(3)", etc., so the SysAdmin always used the no-tag version. The DAA always got the (2) version. Etc. (But note that this was just a reference to the table, not a copy of the real person table.)

Ok so here is what ive done so far

1. Created a table called components with just component ID and The Component Description field

2. Created a Machine Specs table with Service TAg # for the machine as primary key, Component ID, Specification e..g DVD+R or 10GB e.t.c. anda Date Installed field as was suggested.

3. The Main Table Which is the PC Info which has the Service Tag number, Type, Model, Age and Status

4. The User Assignment Table which has the Tag #, the user Badge # and The Date of assignment

5. The User Information table which stores the other details such as names and departments etc


Is this what Im supposed to be doing and is it correct so far. Im appreciating the help so far.

The Age field shouldn't be the age of the PC since that changes every day. It should be the purchase date or inservice date.
Your structure is getting better but is not yet correct. You still don't have a relation table.

tblMachine
MachineID (autonumber primary key)
ServiceTagNum (I guess this number is assigned by your asset system)
EmployeeID (foreign key to employee - however, if you want to track history, this relationship would be many-to-many rather than one-to-many so this field would be moved to the relation table along with the start/end dates of the assignment.
etc.
tblComponents:
ComponentID (autonumber primary key)
ServiceTagNum (assigned by your asset system)
etc.
tblMachineComponents
MachineID (foreign key to tblMachine)
ComponentID (foreign key to tblComponents)
InstalledDate
SerialNumber
tblEmployee
EmployeeID (primary key)
FirstName
LastName
etc.

Im gona give this a litle break but you guys have been rather helpful .. Thanks. Its encouraging to know that i can undertake any task and if i rrun into probs i can link some one.

I'll be sure to add some more questions as they arise.

Okay it turns out that someone is doing a printer inventory that they want me to add to this database. Im going to attach a copy of the Relationships and the tables that i have so far. By the way i cant over-emphasize the magnitude of the gratitude that im feeling.