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

re: Format of tables

"Darius Blaszijk" <dhkblaszyjk@zeelandnet.nl> wrote in message
news:41060ef3$0$13577$fb624cd1@morenews.zeelandnet .nl...[color=blue]
> 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
>[/color]

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.

re: Format of tables

"Rowland Banks" <banksr0@hotmail.com> wrote in message
news:ce544k$6mp$1@sparta.btinternet.com...[color=blue]
>
> "Darius Blaszijk" <dhkblaszyjk@zeelandnet.nl> wrote in message
> news:41060ef3$0$13577$fb624cd1@morenews.zeelandnet .nl...[color=green]
> > Hello,
> >
> > I have the following problem; In my application I have certain items[/color][/color]
that[color=blue][color=green]
> > 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
> >[/color]
>
> 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.
>[/color]
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