1:1 relationship problem

_Nickel_

Registered User.
Local time
Today, 22:12
Joined
Nov 11, 2003
Messages
31
Hi,
I am working on a database of the inventory of PC-Hardware and Software. The hardware is basically divided into PCs and Displays both of which will have a seperate table. Each PC and each Display should have an InventoryID (which Access will automatically assign) but an InventoryID may only be assigned to either a PC or a Display.
So I created an InventoryID table which is linked to both the PC and the Displays tables ina 1:1 relationship (by having the InventoryID be a primary key in all three tables). The problem now is that an InventoryÍD can be assigned to both a PC and a Display.
Is there any way to get Access to only allow every InventoryID to be linked to either a PC or a Display but not both? Or is there even another way to solve this problem without combining the PC and the Display table?

Thx
 
Why do you need separate tables? Why not use one table and have an equipment type field that tells you if it's a PC or a display?
 
go with niels idea...
 
That is what I wanted to do but my "boss" wants me to do it in seperate tables so that they can later on add extra tables for telephones etc.
 
Tell your boss B.S. !

Anything having the same type of ID should be in the same table regardless off the "unit" if your talking hardware, simply add a field type to the table (combo box) allowing the selection of the type of hardware (PC/Display/Telefoon) If you so desire you can seperate the input filling the type field to a default selection.

I however can imagine working with seperate tables for detailed information regarding the hardware. For a pc its intresting to know Internal mem, cpu speed and such whereas a phone or display doesnt have anything simular. Thus if you are storing all this in 1 table you get loads of empty fields all over the place which is probably what your boss wants to prevent.

The basic idea would be:
tblKeyTable
HardWareID
OwnerID
MaybeMoreGeneralDataFields
TypeOfHardWare

tblPCs
HardWareID
CPU
IntMem
HD
etc.

tblDisplay
HardWareID
etc.

Simularly you can do it differently. Using a small table:
tblKeyGen
HardWareID (AutoNumber prob)
Taken (true/false)

Then on creation of a new hardware in any given table you first create a new record in tblKeyGen (Taken = True) Take the ID and stick it in the hardware table.

I think i would go with the first solution.

Hoping i helped you along.

Regards

The Mailman
 
Go with a normalised design. Have done this type of application in a previous life and no doubt if you are going to assign asset numbers and you want no duplication then one table is the answer else

You need a routine that will create the next number by adding 1 to Max from Union of all the asset numbers from all the tables for different equipment that have an asset number, if you see what I mean


len B
 

Users who are viewing this thread

Back
Top Bottom