* Taylor Lewick
> Hi all. I am trying to build a small simple database for a
> lending library.
>
> Basically, I have a table of resources to be lended out, and a
> table that will store a user's name and what they have borrowed.
I think you need a third table to store what the users have borrowed, it
should not be stored in the user table.
> My problem is that we may have more than one copy of a given
> resource say a book.
> So I thought I would just create each row in the resource table
> as an auto_increment integer and assign that the primary key...
>
> How can I make that auto_incrementing field the foreign key and a
> compiste primary key in the user table?
If I understand your problem correctly, three tables would solve it:
USE TEST;
CREATE TABLE user (
uid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(30),
INDEX (username));
CREATE TABLE resource (
rid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
resourcename VARCHAR(30),
INDEX (resourcename));
CREATE TABLE borrowed (
uid INT NOT NULL,
rid INT NOT NULL,
borrowed DATE NOT NULL,
expected_back DATE NOT NULL,
UNIQUE (rid),
INDEX (uid,borrowed));
The unique key on the 'rid' column will prevent you from registering a
resource more than once, i.e. a resource can only be borrowed once at a
time. When it is redelivered, the row from the 'borrowed' table is removed,
and the resource ('rid') is "free" for a new borrowing. The other index is
for listing the resources borrowed by any single user. If the number of
borrowed items was very big and the length of the periods for which the
resources are borrowed are long, I would add an index for the
'expected_back' column too.
Some test rows:
INSERT INTO user (username) VALUES ('adam'),('ben'),('cindy');
INSERT INTO resource (resourcename) VALUES ('a book'),('a CD');
For each resource borrowed, you add a row in the 'borrowed' table.
# adam borrows a book
INSERT INTO borrowed VALUES (1,1,now(),now()+interval 7 day);
# ben borrows a CD
INSERT INTO borrowed VALUES (2,2,now(),now()+interval 7 day);
# cindy wants to lend a CD...
INSERT INTO borrowed VALUES (3,2,now(),now()+interval 7 day);
ERROR 1062: Duplicate entry '2' for key 1
# ben delivers the CD:
DELETE FROM borrowed WHERE uid=2 AND rid=2;
"WHERE rid=2" is enough because 'rid' is unique, including the 'uid' is just
an extra precaution, making sure the right row is removed.
# now cindy can have it:
INSERT INTO borrowed VALUES (3,2,now(),now()+interval 7 day);
If you want to keep the history, you could have a 'history' table identical
to the 'borrowed' table, except for the uniqueness of the 'rid' column:
# copying the structure of a table, without indexes/data:
CREATE TABLE history SELECT * FROM borrowed WHERE 1=2;
Before you remove any row from the 'borrowed' table, you issue this query:
INSERT INTO history SELECT * FROM borrowed WHERE uid=2 AND rid=2
Note that the WHERE clause is identical to the WHERE clause of the DELETE
statement.
# current number of borrowed resources:
SELECT COUNT(*) FROM borrowed
# current number of late deliverances:
SELECT COUNT(*) FROM borrowed WHERE expected_back < NOW()
# 10 users with highest count of borrowed resources:
SELECT username, COUNT(*) cnt
FROM user
NATURAL JOIN borrowed
GROUP BY username
ORDER BY cnt DESC
LIMIT 10
Note that multiple users with the same username would be grouped together in
this query. If this is a problem, change 'username' to 'uid,username' in the
field list AND in the GROUP BY clause.
# current late deliverances:
SELECT username,resourcename,borrowed,expected_back
FROM user
NATURAL JOIN borrowed
NATURAL JOIN resource
WHERE expected_back < NOW()
ORDER BY username,expected_back
The NATURAL JOIN is a 'magic' join between columns with the same name. The
above statement is the same as this:
SELECT username,resourcename,borrowed,expected_back
FROM user u,borrowed b,resource r
WHERE
u.uid = b.uid AND
r.rid = b.rid AND
expected_back < NOW()
ORDER BY username,expected_back
Hope this helps,
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
