Finding a basket consisting only from elements owned by a user

[TheDude83]

Hey,

 

i need a lil bit of help for the app I'm creating using PHP and MySql. Here's the outline:

 

There are 1.000 different elements, let's say balls numbers from 1 to 1.000. A user gets 50 random different balls (12,38,189 etc.). On the other hand we have 100 baskets, in every basket there are 5-10 different balls.

 

What's the easiest way to find baskets, that consist only from balls owned by the user. And how to find baskets, that only have only 1 ball not owned by the user.

 

If any one could point me in the right direction i would be very thankful!

[chronister]

Are the baskets owned by users or are they simply random baskets that may or may not hold balls that match a particular user?

 

Can numbers be owned by more than 1 person? If a user gets balls 12, 48, 555, 632.... can anyone else get those balls?

 

Do you have any existing tables set up or ideas on how your going to do it. Give a little more information and a solution can be found.

 

Nate

[TheDude83]

We are only talking about solution for one user (as it will apply to multiple users as well).

 

To clarify a bit more:

- there's an infinite amount of balls of each kind (so, ball #1 can be drawn multiple times)

- user draws x random balls

- every basket draws n random balls

- baskets them selfs aren't owned, they only represent a group of balls

- we only want to find baskets, not to assigned or anything

 

The simplest way to do this is:

- we have every ball the user owns in db, as a single record

- every basket is represented as a string of ascending numbers:

  - basket #1: 3,9,19,567

  - basket #2: 234,567,987,992

  - basket #3: 187,243,456,673,826,956

- we create every possible combination of user's balls and search it against the baskets.

 

The only problem is, that's a lot of searching. I'm wondering is there a smarter way of doing this.

 

[chronister]

I would not put all the items in a single record seperated by comma as that is very difficult to search against. Use a joining table. Joining tables typically only contain 2 fields.

 

so you would have a table that represents users with a user_id, and another table maybe called user_balls that has the user_id and a ball number, so only 2 columns. Then you can search based on user_id to retrieve all the balls the user owns.

 

Same thing for the baskets. basket_id and ball_number in the joining table. That will allow you to join records based on what the user "owns".

 

Thats the best I can come up with at 3:30 am.... going to bed.... will check back in the morning.

 

Nate

[TheDude83]

Yes, of course a db looks like you described. But the problem with your solution is, that this join would find all baskets that contain at least one ball owned by the user. And we want to find only baskets that only contain balls owned by the user.

 

I guess we could store the summary number of balls in each basket. Do the join as you described, and then loop thru the results, and remove those baskets, that weren't selected as whole. Witch would also allow us to find baskets, that contain only 1 ball not owned by the user.

 

I'll have to test it on a real database, but this could work. Thanks for your help!

 

[chronister]

If your database structure is correct, then you can basically find/eliminate anything you want. A properly written query would only return results that meet the criteria that you are looking for, whether that be a basket that had all balls owned by a user, or a basket that had all but 1 owned by a user.

 

DB structure is key to creating good queries and getting data from it. If you separate the results with a comma, you will have to make the DB engine work harder to separate the data and look through it.

 

Take a look at this slideshow on sql anti-patterns.. I just finally looked at it last night and it has some good information and solutions to doing things "right" and why database structure is important to efficient queries.

 

http://www.scribd.com/doc/2670985/SQL-Antipatterns

 

Nate

[TheDude83]

I haven't used any of the anti-patterns you mentioned. I'm aware of all of them, and create my db's properly.

 

So we have tables:

 

balls (ball_id)

users (user_id)

users_balls (user_id, ball_id)

baskets (basket_id)

baskets_balls (basket_id, ball_id)

 

but you can't query such db to get whole baskets that will have only a combination of balls owned by a user.

 

Only solution i can think of now, is to add column "count" to "baskets_balls", that would hold the value of total amount of balls in each basket. Then i can do a simple join, and then filter results on app side, to remove baskets that where not selected as whole.