SQL Server 'object'/'property' relational tables linking to other tables

Question

Excuse the poor title, i can not think of the correct term.

I have a database structure that represents objects, objects have types and properties.

Only certain properties are available to certain types.

i.e.

Types - House, Car

Properties - Colour, Speed, Address

Objects of type car can have both colour and speed properties, but objects of type House can only have colour, address. The Value for the combination of object, type, property is stored in a values table.

All this works, relationships enforce the above nicely.

My dilemma is that I have another table i.e Addresses. This table has AddressID.

I want to somehow join my address table to my object values table.. is there a neat way to achieve this??

[UPDATE] - More detail

I already have 5 tables. i.e.

Object

Properties

ObjectTypes

ObjectPropertyValues

ObjectTypeProperties

These tables have relationships which lock which property values can be assigned to each type of object.

An object maybe have a name of 'Ferrari' and the type would be 'car' and because the type is car I can set a value for the colour property.

The value though is numeric and I want to be able to join to a colourcodes table to match the id.

Answer 1

First, a "relation" in Relational Databases is a table - it does not refer to the relationships between tables. A relation defines how pieces of data are related - to a key.

In relational modeling, each entity is normalized, so one model for you would be 4 tables:

Car (Colour-FK, Address-FK)

House (Colour-FK, Speed)

Colour (Colour-PK)

Address (Address-PK, Address-Data)

In relational model, cars are not houses and you typically would be extremely unlikely to model them in the same table.

One might argue, that in fact, the valid colours for houses and cars are very different (since the paints are not equivalent), and thus one would not ever combine the two tables based on colour in a real world application.

Possible other modelling considerations might be where the car is garaged - i.e. an FK to a House or an FK to an Address - interesting problem there. Then if you had keys to cars and houses, they could both be part of key rings, in which case you would probably model with link-tables representing the keys.