One-to-less-than-one relationship

[neo651]

Firstly, I know that title doesn't make much sense, but it's the only thing I could think to call this situation.

Secondly, I know my table structure isn't kosher with a lot of people but it's what I'm going with so let's just ignore that part.

So I've got a database tracking assets in a company. I've decided to consolidate any information related to each asset into a single table called tblProperties. The first issue I ran into there is that some properties need to be unique and some don't (i.e. Serial numbers, MAC addresses, IPs are unique; but Brand, Processor and Location do not need to be unique). So what I ended up doing is creating tbl_Unique_Properties and tbl_Standard_Properties and then create a relationship back to tblProperties which then has a relationship to the actual asset table.

The problem I'm running into with this structure is that one value in each of tbl_Unique_Properties and tbl_Standard_Properties could be associated to the same object in tblProperties, but objects in tblProperties need to only be associated to one value out of the two tables (hence the concept "one-to-less-than-one relationship").

Please advise. Thank you.

tblProperties
PropertyID -pk
PropertyClassID -fk

tbl_Unique_Properties


UniquePropertyID -pk
PropertyID -fk
UniquePropertyValue

tbl_Standard_Properties
StandardPropertyID -pk
PropertyID -fk
StandardPropertyValue

[June7]

Not understanding reason for these 'properties' tables. This data is attributes of the asset and should be in the Assets table. Brand, Processor and Location values can be selected from 'lookup' tables. Want to provide db for analysis? Follow instructions at bottom of my post.

Have you checked out the template asset tracking db?

[neo651]

The database has 57 different attributes, too many to put in one table, that's why I broke it out into its own table.

And yes, I have seen the asset tracking template, it's insufficient for our needs.

[June7]

A table can have 255 fields.

What is the relationship between tblAssests and tblProperties? There will be many properties records for each asset? Is there a fk field in tblProperties for the asset ID?

Why do unique and standard properties need to be in separate tables?

[neo651]

tblProperties does a fk for the AssetID and I made unique properties its own table so that I can set the column where the values are stored to be unique.

[June7]

I am bogging down with trying to visualize manipulation of this data in my head. Want to provide db for analysis?