|
|
Posted by Rik on 10/25/06 00:38
Daz wrote:
> Rik wrote:
>> I have told you several times an index on 2 columns is perfectly
>> possible. Make the index:
>> ALTER TABLE `user_books` ADD UNIQUE (
>> `user_id` ,
>> `book_id`
>> )
>>
>> Voilα.
>
> Hi Rick. Sorry, I believe that I have also mentioned several times
> that
> I can't, as each user has one row per book, meaning there won't be any
> unique uids, and if there were, it wouldn't be for very long at all.
> The book_ids in the user table will also be duplicated, as more than
> one user is likely to have the same book.
You don't seem to get it.
Each user does NOT have one row per book. Each user has ONE row per book
THAT HE OWNS.
Table USERS:
user_id PRIMARY(/UNIQUE)
user_name etc....
Table BOOKS
book_id PRIMARY(/UNIQUE)
book_title etc....
Table USER_BOOKS
user_id ---|
|-> UNIQUE
book_id ---|
And those 2 id's there are combined unique, for the user will have only one
copy of one book, or if he has several, add a field 'amount', and
increment/decrement that as fit. A field does not have to be unique for
such an index, a COMBINATION of fields (mostly one, but an arbitrary amount
suffices) has to.
Hopefully this way I have shed some light on the subject, I cannot tell you
any clearer.
--
Grtz,
Rik Wasmus
[Back to original message]
|