Problems with DB-relations, DB-construction and a suitable SQL-query to use
I have a URL with 3 links:
They all send a number (1-3) to the receving asp page that will retrieve that number, whichever link was clicked. The asp page will also perform a search in the database consisting of 5 records (called "Product 1"..."Product 5") all stored in a single column (named "Products").
Collection 1 consists of Product 1, 2 and 5.
Collection 2 consists of Product 2, 3 and 5.
Collection 3 consists of Product 1, 2, 4 and 5.
Then I would write something like SQL="Select * from MyTable.... (please fill in the rest) and run the SQL against the DB.
Lastly I would loop trough the recordset to display the selected products.
My question now is. How should these records best be stored to make the database search as simple as possible? One collection can consist of many different products and a product can be part of several different collections. I don't know how to handle this relationship and perform a search in (accordance with the links above) in an efficient way. It would be nice to just write the SQL like SQL="Select * from MyTable where CollectionID= (the number retrieved from one of the three links)"
<a href="show.asp?collection=1">show fashion collection 1</a>
<a href="show.asp?collection=2">show fashion collection 2</a>
<a href="show.asp?collection=3">show fashion collection 3</a>
As a suggestion, you'll need two tables -- a Products table and a Collections table. The Collections table can be as simple as just two columns -- the Collection id and a foreign key to the Products table. This will allow a Product to be part of more than one Collection. The sql statement for this would be:
"Select * " & _
" From Collections As t1 " & _
" Left Join Products As t2 " & _
" On t1.foreign_key = t2.product_id " & _
" Where t1.collection_id = " & collectionID & "" & _
" Order By t1.collection_id, t1.foreign_key;"
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)