"Darius Blaszijk" <dh*********@zeelandnet.nl> wrote in message
news:41***********************@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.