SEO

#1 (**permalink**) 02-28-2008, 09:24 AM

Hi

I have two tables, User and Book, in Book is a foreign key to UserID
in the User table. (Think of it as an extremely simple library
system).

How would I integrate Integrity Constraints so that 1 user can only
loan/belong to 6 books? I was looking into Stored Procedures/
Functions but was not sure how to go about incorporating this into the
database...

I would be extremely grateful for any help

Thanks

Lawrence

#2 (**permalink**) 02-28-2008, 09:25 AM

On Mar 15, 9:59 am, "Lawrence" <lawrence.h...@gmail.com> wrote:
> Hi
>
> I have two tables, User and Book, in Book is a foreign key to UserID
> in the User table. (Think of it as an extremely simple library
> system).
>
> How would I integrate Integrity Constraints so that 1 user can only
> loan/belong to 6 books? I was looking into Stored Procedures/
> Functions but was not sure how to go about incorporating this into the
> database...
>
> I would be extremely grateful for any help

>
> Thanks
>
> Lawrence

I posted the following response previously - but I can't see it here
so I'll post it again. Apologies if it now appears twice...

Just thinking out loud I think it's a mistake to have the FK in Book.
Instead I'd structure it something like this:

book(book_id*,title,etc)
user(user_id*,name,allowance,no_on_loan)
loan(user_id,book_id,out_date,due_date)

Maybe a statement like this could then be used to maintain an FK
constraint. (It's a redundancy but I'm not sure if there's any way of
tying an FK to derived data):

UPDATE user SET no_on_loan = (SELECT count(*) loans FROM loan WHERE
user_id = myuser GROUP BY user_id) WHERE user_id = myuser;

FWIW, personally, I'd manage the constraint in my interface (I'm
imagining some php front-end to this db) - but that's more to do with
my general ignorance of mysql constraints than anything else.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Integrity Constraints?