One To Many SQL Table

How best to do it?

[Nick_W]

Hi all,

Just a quickie, I can't think of a better way to do this, and I'm not sure one exists but I'd like that confirmed/discussed if possible ;)

I want to allow users to add products to a 'basket' (it's actually a wish list thingy but the points the same). I'd envisioned a table with the user id and a colum containing a comma delimited list of prd id's.

id ¦ products

2 ¦ 2,343,4,95,23

Is that how this kind of thing is done?

Cheers!

Nick

[Robber]

Hi Nick,

Do you need to account for users being able to select a variable quantity of each product?

If you do I guess one way would be to have a table with

userID ¦ prodID ¦ quantity
--------------------------

Rather than a comma separated list of product ids, have a composite primary key on userID and prodID and have each product as a new record in the table.

Although, perhaps for a wish list you wouldn't need that!

Cheers

[Nick_W]

Yep, don't need to worry about quantities. Is that still the way to go? Seems like many many records.....?

Nick

[DrDoc]

I would do it the other way around.

One line for each product. Whenever a user adds that product to his/her list, you add the ID.

prod ¦ user
243 ¦ ,2,5,67,

Let a single comma be the initial value for the user column. Just make sure you always add a comma after the user id. Then, all you have to do is find all rows "... WHERE user REGEXP ',2,'" for example...

[globay]

Nick,

what exactly of is the purpose of the wish list?

Do you have a wishlist, because the product is not available, thus they can't buy it, when they are shopping, or do you have it, incase somebody decides to buy the products later.

Also you might want to think about the number of users, who would use this feature, and the number of products, they ususally add to the database.

If the size of the database is not VERY important, then I would stick with Robber's suggestion, dropping the quantity column. It gives you the best control over the data.

When users log in, you probably want to display the items from the wishlist. It would be difficult to query using DrDoc's database.

If YOU rather want to see, which products have been added (most) to the wishlist, then I would go with DrDoc's model.

If you are going to do both, use Webber's db!

Just my 2 cents ;-)

[Nick_W]

That makes sense.

Here's what it's for: I want people to be able to browse a db of products and rather than click through to the buy page, to be able to add them to a 'for later' or 'bookmarks' type affair.

Then at the end of their browse they can go to their picks and click on each link to look in more detail at the product or they can have them emailed etc....

So, my original or robbers. I think people will use it, not hugely though. So, I really want to be able to easily find all the items for one visitor. BUT I'd also like to do some things with the products colum like compile a 'most popular' list......

With me?

(thanks everyone!)

Nick

[dingman]

I do something simillar to track which messages each user has read on my discussion site. I started with

create table foo ( 
  uid  uid_type not null references user_table(uid), 
  mid  mid_type not null references message_table(mid), 
  primary key (uid, mid) 
); 

But as you might guess, the table got big fast. The solution I'm using right now is a third column to track the date. Anything over a certain age gets forgotten, so the table stays at a more-or-less constant size. It still *could* grow to be the product of the size of the user table and the message table if everyone decided to go back and read every message in the forum within the expiration period for those rows, but it won't. I actually think I probably should have used the Postgres 'array' type so the table wouldn't get any more rows than I have users, but this method does work well for both kinds of look-ups. I could easily find out who read the most messages in the last week or which messages were most often read.

[globay]

Nick,

I wouldn't use a time column, for 'expired' wishes ;-)

You want to sell the product, even if they chose it long time ago, and forgot it in the mean time.

[Nick_W]

Actually I'd already got as far as a time colum and thought it a rather good idea? After 1yr with no update surely I can ditch it? ;)

Nick

[globay]

After one year, I guess you should be fine, deleting the items ;-)

How do you do that?

Do you use the last date, they added some product to the database, or do you save the date for each product? That could influence the db structure discussion.

[Nick_W]

Just have it as a timestamp field at the end of the list of columns. When an item is added/subtracted the timestamp will be updated automatically. That way I can just run a cron job on it every month.

It also allows me a year to learn how to write a cron job hehehehe.....

Nick

[drbrain]

Robber and dingman have the correct way to go, because that way properly maps the data, and allows you to use relational algebra to do fancier things, like find out (as suggested by globay) what is in the most baskets, without having to do any custom coding by yourself.

My SQL is (very) rusty, the top 10 most chosen items should be something like:

SELECT product.product_name, COUNT(product.pid) AS count FROM product, basket WHERE product.id = basket.pid GROUP BY product.id ORDER BY count LIMIT 10;

(Maybe the COUNT(product.pid) is supposed to go in a sub-select, I forget, must get the books out one of these days.)

To delete items more than 1 month old, your cron job should run a SQL query something like:

DELETE FROM basket WHERE date < NOW() - 24*60*60*30;