Hi,

I have no clue how to design a query for the following scenario in sql:
Suppose you have 2 tables with app. the following structure:

CREATE TABLE figure (
`id` bigint(20) NOT NULL auto_increment,
`name` varchar(255),
PRIMARY KEY (`id`)
)
CREATE TABLE fig_preferences (
`id` bigint(20) NOT NULL auto_increment,
`pref_key` varchar(255) ,
`pref_value` varchar(255) ,
`fig_ref` bigint(20) default NULL,
PRIMARY KEY (`id`),
KEY `FK_fig_ref_` (`fig_ref`)
)

This database should be able to store figures with associated
key-value-pairs. Now I have a set of key-value-pairs that I have to
find the names of figures for that don't have any additional
key-value-pairs. For example suppose you have the following data in
this sample database:

Table Figure:
id | name
--------------
0 | fig1
1 | fig2
2 | fig3

Table fig_preferences:
id | pref_key | pref_value | fig_ref
--------------------------------------------------
0 | color | blue | 0
1 | shape | rect | 0
2 | color | blue | 1
3 | color | red | 2
4 | shape | rect | 2

Now if I would like to know the names of all figures with color=red and
shape=rect it should return fig1 and fig2, but not fig3. With other
words the query should return all figures that don't have any
key-value-pairs in the db that are not in the set of key-value-pairs in
the query. Since sql doesn't support any set operations like
intersection I have no clue how to proceed.

Thanks for any help.

Steffen

--------------
0 | fig1
1 | fig2
2 | fig3


--------------------------------------------------
0 | color | blue | 0
1 | shape | rect | 0
2 | color | blue | 1
3 | color | red | 2
4 | shape | rect | 2


DBD is an experimental, non-RMDB database.
The following is DBD's script to implement a solution:

(; Create a db item named color)
(new 'color)

(; Create a db item named shape)
(new 'shape)

(; Create a db item named figure)
(new 'figure)

(; Create a figure named fig1
whose color is blue
and shape is rectangular)
(new)
(create figure instance (it))
(create (it) name 'fig1)
(create (it) color (val+ 'blue))
(create (it) shape (val+ 'rectangular))

(; Create a figure named fig2
whose color is blue)
(new)
(create figure instance (it))
(create (it) name 'fig2)
(create (it) color (val+ 'blue))

(; Create a figure named fig3
whose color is red
and shape is rectangular)
(new)
(create figure instance (it))
(create (it) name 'fig3)
(create (it) color (val+ 'red))
(create (it) shape (val+ 'rectangular))


(; Find all figures whose color is blue)
(; Finds figure 1 and 2)
(and (select figure instance *)
(select * color blue))

(; Find all figures whose shape is rectangular)
(; Finds figure 1 and 3)
(and (select figure instance *)
(select * shape rectangular))

(; Find all figures whose color is red
and shape is rectangular)
(; Finds figure 3)
(and (select figure instance *)
(select * color red)
(select * shape rectangular))


See following examples with similar queries:
www.dbfordummies.com/Example/Ex004.asp
www.dbfordummies.com/Example/Ex005.asp
www.dbfordummies.com/Example/Ex039.asp

No, you do not havea clue as to what a table is. That is why your
queries will be awful. Auto-increment cannot ever be a key by
*definition*; it is an exposed phsyical locator basd on the internal
state of the machine while a key is a subset of attributes of the
entity itself. Data element names like "id" and "name" are too vague
to be used even in sample code.

What you are trying to do is called an EAV design and it does not
work. Google any of the articles on this fallacy.

<steffen.dienst (AT) gmail (DOT) com> wrote

I am not sure I follow.
color=red returns line 3.
shape=rect returns lines 1,4.

Therefore
color=red AND shape=rect returns lines 3,4 which when combined point to
fig_ref 2 which is fig3.

Therefore
color=red AND shapre=rect returns fig3.

How do you get it to return fig1 and fig2?

Am I missing something?

The other question I want to ask is, do you have queries such as:
color<>red?
If you ever need this type of query, you have a huge problem and this will
result in full table scans.

And what about:
color=red OR shape=rect
This type of search will also result in full table scans though a lack of
definitive index use.

If you need to do any of the above, I suggest you look at other methods than
what you have above.

Are the attributes always consistent and static? If so, I strongly
recommend that you make each attribute a column value. Your queries will
be much much easier and faster. Make sure your indexing across the values
if using a set of concatenated indexes makes best use of filtering your
records.

I can appreciate why you wish to design the database tables the way you do
to allow for dynamic attributes, but if they are not necessary, don't do
this. It will cause severe scalability issues. It will also cause
performance issues if the user needed to do basic stuff such as see each fig
in a table with all its attributes. The cost to build that table can bring
your database down if all your users are doing similar things.

AMO

AMO wrote:

AMO wrote:
[]

Good advice AMO. Now do you see why, especially in this group, that we
suggest designing tables with real attributes for primary keys? It is
just way too easy for the newbies to fall into this autoID trap. Even
in our other discussion, I knew you knew this.

To steffan,
Change your design. Read the topic Joe Chelko warned you about: EAV.
Consider starting from scratch with an Entity relationship diagram
(ERD). You'll be glad you did.

Ed
Good advice AMO. Now do you see why, especially in this group, that we
suggest designing tables with real attributes for primary keys? It is
just way too easy for the newbies to fall into this autoID trap. Even
in our other discussion, I knew you knew this.

To the

"Ed Prochak" <edprochak (AT) gmail (DOT) com> wrote


Now now, lets not get carried away here!

The structure should be turned attribute based, but there is no reason why
he cannot use a sequence or GUID for his primary id.

If his attribute requirement is dynamic, he will require another solution.

Now.....what would you suggest if this was the case! ;0)

AMO

Validation? Verification? Portability?

More research.

--CELKO--

"--CELKO--" <jcelko212 (AT) earthlink (DOT) net> wrote

It depends on how you design a distributed database. The primary key does
not need to be portable as it will be different in different layers.

That's always the easy answer isn't Celko? I mean, your views are well
respected on a conceptual level, but your have never said anything remotely
acceptable towards an acceptable practical solution when there is a
requirement. There are instances where one needs dynamic attributes. There
is so much you can do with table partitioning to group them. Imagine a
database to hold the layout on a screen and to store the objects created.
Some objects will have HEIGHT, WIDTH, COLOR etc, but some objects such as
Windows will have VIRTUAL-HEIGHT. Others will have other properties not
present in other objects. You can either go down a path where every
identifiable attribute is put in the table, or seperate every object type
into its own table - but this will mean a database change everytime a new
object type is created.

Alternatively, you can look at solutions which whilst are not conceptually
perfect, offer a practical solution. Of course, you can also look at other
technologies such as object databases.

However, the principles you apply are based on the relational condition that
you are in control of all information and all its dependencies in order to
create those relationships. There are scenarios where we do not know what
properties an item has that needs to be registered. Whilst I disagree with
the other person in terms of how he has implemented his solution, on the
assumption that his need was a dynamic set of attributes, I do accept that
he did his best to achieve this and try to seperate property/value to avoid
database scans having to parse delimited strings such as
color=red,size=large requiring full table scans.

The world of databases is changing and in todays world there is a greater
requirement for integration where information may come from different
sources, sometimes with only a subset of the desired information due to some
information on one database being essential information whereas on another
the concept does not even exist. A lack of information until certain points
in time are also common such as surfing a website until you buy something.

Again, assuming that you had a similar set of requirements, how would you
overcome this problem? Take an online retailer for example and his stock
list. Lets assume the website like Amazon sells books - this would require
an ISBN number. But then, other items such as DVDs do not. Imagine that
Amazon also sells tyres, then attributes of the product would also include
size and speed etc. When do you draw the line between adding an additional
attribute and populating it when relevant and creating a seperate table
because of differences in the entity? Now in the above example, there is
sufficient differences to have seperate entitites, but in other scenarios
such as Windows objects, the distinction is not so clear cut. You don't
really want a database change every single time you add a new object type.

The other person may have went about things the wrong way, but your
conceptually perfect solutions rely on information and control which may not
be there.

AMO

You make a good point.

Below is an alternate solution to model/query figures with various
shapes and color:

(new 'red 'color)
(new 'blue 'color)

(new 'rectangular 'shape)

(new 'fig1 'figure)
(create fig1 color blue)
(create fig1 shape rectangular)

(new 'fig2 'figure)
(create (it) color blue)

(new 'fig3 'figure)
(create fig3 color red)
(create fig3 shape rectangular)


(; Find figures whose color is blue)
(; Finds figure 1 and 2)
(select * color blue)

(; Find figures whose shape is rectangular)
(; Finds figure 1 and 3)
(select * shape rectangular)

(; Find figures whose color is not blue)
(; Finds figure 3)
(select * color (not blue))

(; Find figures whose color is red
and shape is rectangular)
(; Finds figure 3)
(and (select * color red)
(select * shape rectangular))

(; Find figures whose color is red
or shape is rectangular)
(; Finds figure 1 and 3)
(and (select figure instance *)
(or (select * color red)
(select * shape rectangular)))