Good DataStructure for Representing Metadata Attributes?
Date: Tue, 26 Feb 2002 14:58:29 -0500
Message-ID: <3C7BE8E5.35B723F6_at_thankyou.very_much>
Suppose you want to persist a set of metadata attributes for objects. Two common ways of doing it:
1)
objectid key value 78 phone 555-1212 78 addr Main Street 79 phone 222-2222 79 addr Green Street
(keys could be normalized of course but I skip that for this example)
2)
objectid phone addr
78 555-1212 Main Street 79 222-2222 Green Street
The first approach has several advantages
- It is flexible. That is you do not need to change the schema to add
new attribute types.
- It is normalized in a sense that if an attribute does not have a value you can represent it by its absense in the table istead of duplicating NULL in every feild that does not have a value. In my case this is actually a critical thing because there are a lot of attributes whose values are not set.
Overall the first approach serves my case much better than second
approach. However it has important disadvantages in that the search by a
combination of attributes as
well is sort by attributes is very cumbersome and slow. For example if I
wanted to find all objects where phone number is 555-1212 AND address
equals "Main Street" I would need to
do a self join like
select t1.objectid from mytable t1, mytable t2 where t1.objectid=t2.objectid and t1.phone='555-1212' and t2.addr='Main Street'. There are many problems here
- very slow when table is large
- you need to do a self join for every attribute in the AND-ed expression.
- If you want to sort by an attribute other than the search key you need to self join yet again.
By contrast with approach 2 all these problems go away:
select id from mytable where phone='555-1212' and addr='Main Street' order by some_field
THE QUESTION: Is there any good datastructure for representing Metadata which has the advantages of approach_1 and the search and sort convenience of approach_2 ? Seems like a problem many people would have run into.
Thank you so much for your response
Dre Received on Tue Feb 26 2002 - 20:58:29 CET