Posted by Rik on 10/24/06 15:10

Daz wrote:
> I think that there is a lot of confusion here. Some people are not
> getting the right idea. Probably because my attampt to explain failed.
>
> Here is a small snippet from my main database reference table. They
> are virtual books, and each book has an id. The last column is not
> needed for this operation:
>
> +---------+--------------------------------+------------+
>> book_id | book_name | is_retired |
>> +---------+--------------------------------+------------+ 2 |
>> 13 Banlow Street | 1 | 299 | Baseball
>> Fans | 1 | 471 | Cherry Blossoms
>> | 0 | 665 | Down by the River | 1
>> | 1181 | I will always Remember | 0 |
>> 1339 | Kimbler: The Beginning | 0 |
>> 1433 | Let the Game Begin | 0 |
>
> And so on... There are just over 3600 entries at present.
>

So, a unique book-id.

> Here is a small extraction from the users table, which shows which
> books the user owns.
>
> +-----+---------+
>> uid | book_id |
> +-----+---------+
>> 3 | 3194 |
>> 2 | 2947 |
>> 3 | 2091 |
>> 3 | 307 |
>> 3 | 1434 |
>> 4 | 3278 |
>> 3 | 1288 |
>> 2 | 3239 |
>> 3 | 2467 |
>> 1 | 991 |
> +-----+---------+
>
> Remember. Neither of these columns contain unique values.

But uid & bookid combined are unique, create a unique index on the TWO
columns at once.

> As you can see. I didn't want to add more than 3600 columns to the
> users book table, as this would mean I get a lot of NULLs which isn't
> very efficient and also bring with it a few more down sides.

Why not add more? With the right indexes, searching will still be fast. You
could even add a foreign key constraint to cascade deletions. You will not
get null-values...

> The only other option I have, is to create a table for each book. That

Brrr, do not do that :-)
--
Rik Wasmus

[Back to original message]