relationship advice... 1

[adsfx]

...sounds like an agony aunt Q!

1)I have an inventory DB where gen.info (includes item ser no., date purchased, condition etc) is stored in main table, then a category table(includes PC, monitor etc)

but, when it comes to category specific info (if it was a PC - then HDD, memory etc..) i get stuck...
at the moment bcos a PC is the only item with many individual fields all category specific fields are stored in the main table...am I setting myself up for a fall!

2)second Q is similar because computers can have many types of Software and Software can be on many computers how do I make a relationship that reflects this?
each piece of S/W needs details related to each PC such as Licence no.

thanx in advance Mark

 

[jrbarnett]

Hi Mark,

Starting with the second question as this is easier, you want two tables:

SoftwareType (TypeID, Description)
- This would include things like
SS -Spreadsheet, DB - Database, OF - Office,

The operating System is a specific type of installed software which would also be in the list.

InstalledSoftware (ComputerID, TypeID, Version, LicenseNumber, Notes)
ComputerID is a foreign key from the general info table.
TypeID is a foreign key from the SoftwareType table.
Version is a string containing details of the software - eg XP Pro SP1a or Office 2000 SP3
LicenseNumber is the license key for that particular PC.
Notes is for any specific details relating to that particular piece of software on that PC - perhaps if you need to install an extra patch, customise the configuration etc.

Now, back to the first question, I would have one table for PC Info.

eg SerialNo, PurchaseDate, WarrantyExpires, RAM, Disk, NICType, CPUType, CPUSpeed, Chipset, Make, Model.

SerialNo is the primary key and is used as a foreign key into the InstalledSoftware table. Operating system is stored in the installedsoftware table, so is not kept here. Other items should be self explanatory.

Peripherals are stored in a separate table:

SerialNo, Type, HostPC, Make, Model, Notes

SerialNo is the device serial no and table's primary key, type is a description of the device (Eg Webcam, Inkjet printer, Laser printer, digital camera, scanner).
HostPC is the serial number of the PC that it is connected to and is a foreign key to the computer details table. Note that this field must be optional - because of items that move around - eg usb flash drives, or network printers that are not connected to a specific PC.
Make and model are obvious.
Notes is for any other info about the device, perhaps print queus for a network printer etc.

John

 

[adsfx]

great advice john heres a *
a couple of q's...
1)PC info is main table
PCinfo(serno) > Inst S/W(serno) > S/W type (typeID)
> Periph (serno)

2)there is no gen.info table?

cheers Mark

 

[jrbarnett]

Hi,

Thanks for the star.
Forgive me if I misunderstood, you but as I thought the geninfo table stored absolutely everything, I have broken down the details into pc info and peripheral info, which is why I haven't included the gen.info table - its data is stored in the two others. However, if you want to put warranty and purchase info into another table away from teh actual techical specifications, this is quite easy to do -
it will be
serialnumber, purchasedate, warrantyexpires etc with foreign keys to pc info and peripheral info.

Obviously the table structure is something to work on though.

John

 

[adsfx]

cheers John,
there is not much point havin a gen info table that links to PCinfo as this is a 1-to-1 isnt it?

Is it ok to have records in the PERIPHinfo table that have no links to the PCinfo table (does this violate referential integrity)?

Does it really matter about ref. integrity?

Mark

 

[jrbarnett]

Yes - any general info table would have a 1:1 link with pc info and periphal info so it is pointless as you say.

It is ok to put information in the peripheral info table with no links to the pc info table - provided that you don't set the required property on the hostpc field to true.
As I said, there are cases when it won't be required - when for example network print servers, networked printers, portable storage devices that move between machines - but details of the equipment should still be stored.

John

 

[adsfx]

thanx again

 

[adsfx]

Hi,
am trying to get the input form for the s/w installed table to open at the correct record.
how do i get the computerID key in installedsw table to populate from PCinfo table when i click form button?

am i making sense...let me know

 

[jrbarnett]

Mark,

Are you trying to open the installed software form from the pcinfo table - where there may (or may not) be related records in the installed software setup?

The easiest option is to set the default value of the ComputerID field in the installedsoftware form to:
=Forms!frmComputerInfo!txtComputerID

where frmComputerInfo is the name of the form, and txtComputerID is the name of the textbox on the form with the computer ID in it.

John

 

[adsfx]

thanx for gettin back john,
did what you said but when the txtbox is bound to the equipID in the installedsw form (which surely it has to be to get its reference) the txtbox stays at 0 (which is the only record in the installedsw table)

if i change the textbox to unbound the ID is shown...
what am i doing wrong?

many thanks Mark

 

[adsfx]

ps.yes am trying to open the installed software form from the pcinfo table

 

[adsfx]

got it sorted using openargs argument of openform command
thanx for your help...dont know why i couldnt get me head round it - big weekend!