One-To-One relationship design issue 2

[rgatrell]

I’m creating an Asset Inventory (using MS Access for now) and have settled on the following design (please note there are other tables and fields involved but I’ve omitted them for readability):

Assets Table:
------------
AssetID (PK)
AssetType
Tag
Make
Model
Serial
etc.

Computers Table:
----------------
ComputerID (PK)
AssetID (FK)
MAC
Hostname
etc.

Monitors Table:
--------------
MonitorID (PK)
AssetID (FK)
Type
Size

The Computers and Monitors tables are in a one-to-one relationship (linked by AssetID) with the "master" Assets table. My reason for this is it makes for a clearer layout and avoids a huge main "Assets" table with redundant fields.

I'd like to have a link between a computer and its attached monitor (some of our computers have multiple displays).

I've tried a few different methods but without success, can anyone come up with a solution?

Thanks very much.

 

[obadare]

a column in monitors_tbl ComputerId int not null references computers_tbl(computerId).

To find monitors attached to a computer:
select * from monitors_tbl where computerId=?

 

[rgatrell]

Thanks for the reply obadare. I'll need to allow nulls as some
computers e.g. servers, won't have monitors attached.

My concern was that as the relationships are one-to-one, won't that mean that they are essentially referencing themselves?

 

[obadare]

If there are no standalone monitors, i don't think it will matter. Every monitor must be attached to a computer, but a computer may be without a monitor.

 

[rgatrell]

Monitors whilst in stock are not currently attached to a computer, however as per your advice I've managed to set it up successfully - thanks for your help!

 

[SQLSister]

I'll also point out that monitors and computers are not necessarily in a one-to-one relationship. I have two monitors on my work computer (as do all the developers here).

"NOTHING is more important in a database than integrity." ESquared

 

[JarlH]

And a monitor may be used with two computers. (Some developers here use two computers, but only one keyboard and one monitor.)

 

[rgatrell]

Just to clear up possible confusion, the relationship between computers and monitors is one-to-many (we also use multiple displays at work).

The one-to-one relationships are between the "master" assets table and the computers/monitors tables.

 

[harebrain]

>The one-to-one relationships are between the "master" assets table and the computers/monitors tables.

No, it isn't. There are two one-to-[zero or one] relationships, one from assets to computers, and another from assets to monitors. There is NO one-to-one relationship.

 

[ceh4702]

If you are managing assets, you may want to know some other things:

Date Aquired
Original Cost
Depriciation
Insurance info
Labor Warranty Date
Parts Warranty Date
BLDG/ROOM
Date Removed/Destroyed
Disposition Code

I dont see a need to link a Computer to a Monitor as lone as you know what building and room it is in. If you really wanted to know this you may want to have a table for computer system where computer system is the primary key and the acutal parts that make up the system are the secondary key. One problem would be when you send in a monitor or computer for repair on warranty or warranty exchange and then you have to unlink the computer or monitor and replace them with some other computer or monitor.

We have a blanket insurance policy for all computers when their warranty expires. So we have to know what is under warranty.

If you do not like my post feel free to point out your opinion or my errors.

 

[rgatrell]

> No, it isn't. There are two one-to-[zero or one] relationships, one from assets to computers, and another from assets to monitors. There is NO one-to-one relationship.

Thanks for the info, that is a much clearer way of describing things. I've not been into DB design for very long and it's good to know the correct terminology!

 

[rgatrell]

> If you are managing assets, you may want to know some other things:

I've already got similar fields in the main table, I didn't list all my columns in my original post for clarity reasons. Thanks for the suggestion though.

> I don't see a need to link a Computer to a Monitor as lone as you know what building and room it is in.

Our users have a tendency to swap their monitors about (for reasons only known to them..). We also like to know for support reasons which particular make / model of monitor is (or should be) in use with a particular PC.

 

[Olaf Doschke]

I wonder if these are 1:0-1 relationships, as computers and monitors have their own PK, you could also have many computers and monitors per asset, so these are just normal 1:n relationships. Only if you define AssetID as both PK and FK you get a 1:0-1 relationship.

Bye, Olaf.

 

[harebrain]

you could also have many computers and monitors per asset, so these are just normal 1:n relationships.

No, and no. Read the OP description: an "asset" is either a "computer" or a "monitor." Period. Your assertion about AssetID as both PK and FK is faulty. Cardinality here comes from the business rules, not anything inferred from the keys. Really, I'd think it bad form to use AssetID for the primary key in the child tables because here parent:child is not a natural join in either case.

 

[Olaf Doschke]

Hello harebrain,

well, generally: if you set up a 1:0-1 relationship, that's what you end up with on the 0-1 side: an ID field being both PK and FK. And that's not wrong or faulty.

I was talking about asset:computer and asset:monitor being 1:n relationships in the OP design. That's what they are, because the table design does not prevent the repeated use of an asset ID.

TheOP said, computers and monitors are 1:1 related via assetID, in your understanding they could not be connected anyhow, as an asset would only be either a computer or a monitor, not both.

Anyway computers and monitors are even in a (wrong) n:m relationship via assets.

That's indeed desired, but not through the assets link, if I take it as correct, that an asset should only be one of the assettypes.

I'd two new table "workstations" and "workstationassets". Then N assets would be coupled by N entries in workstationassets all pointing to 1 workstation.

And to prevent an asset of being both a computer and a monitor you'd define a restriction of the reference of computers to assets, to only reference assets of the correct asset type (computer) and monitors to reference only monitors. So the asset type would restrict the use of the ID as FK in the right table.

It'd be fine if computers and monitor ar general all tables have there seperate PK, still assetID would be a secondary key in these tables, as an asset should also not be two monitors or two computers then.

Another even more general way would be to not define special tables like computer or monitor, but tables attributes, attributesofassettypes and assetattributevalues, with which you can define what attributes there are in general, which asset type has what attributes and what assetattributevalues a concrete asset has additional to the standard attributes in the asset table.

But this kind of normalization level will make it hard to query anything.

Bye, Olaf.

 

[HelloMike]

Am I missing something here? Surely the relationship between computers and monitors is many-to-many: One computer can be attached to multiple monitors (as stated in earlier posts). But one monitor could be attached to many computers (ie. acting as console for a rack full of servers).

Cheers, Mike.

 

[Olaf Doschke]

Hello Mike,

yes, perhaps the OP (rgatrell) should state, what he meant by "monitors and computers being in a one-to-one relationship linked by AssetID."

Or what is an asset here? Is it really a single device eg computer OR monitor (defined by AssetType, not able being both) or does an asset stand for a group of devices? The design as it is, speaks for the group of devices interpretation, as there are two 1:n relationships and so computers and monitors are in an n:m relationship.

But a fields like serial number speaks for the interpretation as assets being only on Or the other. So computers and monitors could not possibly be linked to each other by assetID, as they don't share their serial number.

So there need to be additional table(s) to make the n:m connection between computers and monitors, eg my suggested workstation table.

Bye, Olaf.

 

[rgatrell]

Hi Olaf

Have a look at

http://www.tek-tips.com/viewthread.cfm?qid=860190

(the first post by "willir") for where I took inspiration for my design.

A single Asset can be either a Computer or a Monitor but not both. The foreign key AssetID (present in all three tables) is set to not allow duplicates. In theory the same AssetID *could* appear in both the Computer and Monitor table which would of course lead to confusion...

...However, I've ensured that data entry will not permit this (I'm currently moving the database to SQL 2005 and all my updates and additions are done via views and stored procedures). I have made it impossible (using a GUI) to insert a row in a Computer or Monitor table with an identical AssetID and I could always have a trigger to rollback any potential attempts to do this.

The relationship between Computers and Monitors is one-to-many, i.e. one Computer can have many Monitors attached. This is done by having a Foreign Key (ComputerID) in the Monitors table, linked to the Primary Key (ComputerID) in the Computers table. A monitor cannot be attached to more than one computer (I know in practice this could happen but not in our environment). Computers and Monitors are NOT linked by AssetID.

So far this design is working well but I suppose it may seem overly complex as I could in theory have one big flat table with all the possible Asset attributes - this would certainly make designing a GUI a little easier but I've found it's clearer to view and to query.
I hope I’ve explained my reasons clearly but I’m always open to suggestions on how to improve my design.

Thanks.

 

[Olaf Doschke]

Hi rgatrell,

as you layed it out now, it all makes sense. I didn't notice where you introduced the computerID in the monitors table.

I'd also recommend to not make it one wide asset table containing all possible attributes some device could have.

With SQL2005 you could make use of the xml field type and store individual attributes as xml. that will especially help if you plan to manage all other devices and components with their specific atributes without having to manage a table for each device/asset type.

Bye, Olaf.