Hello,

I am designing a Video Tape Library application to log and search our
videos.
I want to allow the end users to create their own "Properties".
For instance create a Location property, with the fields City/State/Country
and add values to these properties
so they can then search on them.

I have created the following design to do this, but I find it has a number
of problems:

tblProperties
-----------
PropertyID
PropertyName

tblPropFields
-------------
PropFieldID
FkeyPropertyID
FieldName
Sequence

tblPropValues
-------------
PropValID
FkeyPropFieldID
PropValue

tblTapeProperties
-------------
TapePropID
FkeyTapeID
FkeyPropValID


The First Table would hold the various property types like
Locations/People/MediaType etc etc.
The Second table would hold the fields a property type would have like
City/State/Country for the Location property, and the sequence
they should appear in the UI, and a foreign key pointing to the Property
Table.
The third table would hold the actual values for the Properties like New
York,NY USA for the Location property, with a foreign key to the PropFields
table.
And the fourth table would hold which properties the tape has, with a
foreign key to the PropValues table.

There are a number of problems with this which I can find as you can see.
For instance, returning a Property value for a Property which has 3 fields,
like the Location property.
Also sorting the list of Locations by lets say the "City" field for the
Location Property and keeping the value intact.
If anyone can give me any suggestions or pointers I would greatly appreciate
it.

Cheers,
Michael