Format of tables

Hello,

I have the following problem; In my application I have certain items that
have properties.

Item Property
--- --------
ToyBear Hairy, Soft, Brown
ToyCar Brown, Plastic, Wheels
ToyBall Round, Soft, Brown, Plastic

As you might expect I want to do queries on the properties; so property
Brown should yield all items listed above and Plastic should yield only
ToyCar and ToyBall.
The amount of properties is limitless, so making a separate field per
property seems to be madness. My question is how can I make a (or more)
tables that will enable me to search for items by applying one or more
properties?? What structure do I need to accomplish this?

Kind regards, Darius Blaszijk"Darius Blaszijk" <dhkblaszyjk@.zeelandnet.nl> wrote in message
news:41060ef3$0$13577$fb624cd1@.morenews.zeelandnet .nl...
> Hello,
> I have the following problem; In my application I have certain items that
> have properties.
> Item Property
> --- --------
> ToyBear Hairy, Soft, Brown
> ToyCar Brown, Plastic, Wheels
> ToyBall Round, Soft, Brown, Plastic
> As you might expect I want to do queries on the properties; so property
> Brown should yield all items listed above and Plastic should yield only
> ToyCar and ToyBall.
> The amount of properties is limitless, so making a separate field per
> property seems to be madness. My question is how can I make a (or more)
> tables that will enable me to search for items by applying one or more
> properties?? What structure do I need to accomplish this?
> Kind regards, Darius Blaszijk

The obvious thing would be to have two tables - TB_Item and TB_Property.
TB_Property has a foriegn key referencing into TB_Item.

Therefore, you're definition would look something like this:

CREATE DATABASE TB_Item (
name VARCHAR (30),

CONSTRAINT PK_TB_Item
PRIMARY KEY (name)
)

CREATE DATABASE TB_Property (
itemName VARCHAR (30)
propName VARCHAR (30)

CONSTRAINT FK_TB_Item_name
FOREIGN KEY (itemName)
REFERENCES TB_Approver_Type (name)
)

Then, to populate the database for the example of ToyBear given above, do:
INSERT INTO TB_Item (name) VALUES ('ToyBear')
GO
INSERT INTO TB_Property (itemName, propName) VALUES ('ToyBear', 'Hairy')
INSERT INTO TB_Property (itemName, propName) VALUES ('ToyBear', 'Soft')
INSERT INTO TB_Property (itemName, propName) VALUES ('ToyBear', 'Brown')

Hope that is clear, and answers your question,

Rowland.|||"Rowland Banks" <banksr0@.hotmail.com> wrote in message
news:ce544k$6mp$1@.sparta.btinternet.com...
> "Darius Blaszijk" <dhkblaszyjk@.zeelandnet.nl> wrote in message
> news:41060ef3$0$13577$fb624cd1@.morenews.zeelandnet .nl...
> > Hello,
> > I have the following problem; In my application I have certain items
that
> > have properties.
> > Item Property
> > --- --------
> > ToyBear Hairy, Soft, Brown
> > ToyCar Brown, Plastic, Wheels
> > ToyBall Round, Soft, Brown, Plastic
> > As you might expect I want to do queries on the properties; so property
> > Brown should yield all items listed above and Plastic should yield only
> > ToyCar and ToyBall.
> > The amount of properties is limitless, so making a separate field per
> > property seems to be madness. My question is how can I make a (or more)
> > tables that will enable me to search for items by applying one or more
> > properties?? What structure do I need to accomplish this?
> > Kind regards, Darius Blaszijk
> The obvious thing would be to have two tables - TB_Item and TB_Property.
> TB_Property has a foriegn key referencing into TB_Item.
> Therefore, you're definition would look something like this:
> CREATE DATABASE TB_Item (
> name VARCHAR (30),
> CONSTRAINT PK_TB_Item
> PRIMARY KEY (name)
> )
> CREATE DATABASE TB_Property (
> itemName VARCHAR (30)
> propName VARCHAR (30)
> CONSTRAINT FK_TB_Item_name
> FOREIGN KEY (itemName)
> REFERENCES TB_Approver_Type (name)
> )
> Then, to populate the database for the example of ToyBear given above, do:
> INSERT INTO TB_Item (name) VALUES ('ToyBear')
> GO
> INSERT INTO TB_Property (itemName, propName) VALUES ('ToyBear', 'Hairy')
> INSERT INTO TB_Property (itemName, propName) VALUES ('ToyBear', 'Soft')
> INSERT INTO TB_Property (itemName, propName) VALUES ('ToyBear', 'Brown')
> Hope that is clear, and answers your question,
> Rowland.
ADDENDUM:
I just read through your post again and I missed a bit. To extract the
information, use somethign similar to:

SELECT i.name
FROM TB_Item AS i, TB_Property AS p
WHERE i.name = p.itemName
AND p.itemName = 'Hairy'

hope that helps,

Rowland