travishaberman
asked on
How do I relate tables in SQL
Problem: I have a table that contains user information such as name, phone, username, address, etc. I would like for a user to add multiple "books" to their account. I could do this all on one table, but I want them to be able to add as many books as they would like. I am sure there is some way for them to have subtables of books that would have fields such as title, autor, print date, etc. I can make both tables separatly, but how do I combine the book tables with the correct user table. The answer/solution to this question will need to have some "high-level" conceptual information as well as some code.
Thank you EXPERTS,
-TH
Thank you EXPERTS,
-TH
You would normally have 3 tables for this type of relationship: Users, Books and UserBooks
Table Users contains information about all the users
Table Books contains information about all the books available
Table UserBooks contains information about the books users have and relates the Users and book table together.
To add a book to the user account you just add a record to the UserBooks table.
Table Users
========
Username
UserId <- This is a unique key
name
address
etc...
Table Books
========
BookId
Title
Author
etc...
Table UserBooks
===========
UserId
BookId
To select information about what books a particular user has you would do a query like this:
select U.UserName, B.Title
from Users U, Books B, UserBooks UB
where U.UserId = UB.UserId
and UB.BookId = B.BookId
and U.UserId = 123
To select information about what users have a particular book would do a query like this:
select U.UserName, B.Title
from Users U, Books B, UserBooks UB
where U.UserId = UB.UserId
and UB.BookId = B.BookId
and B.BookId = 'XYZ'
Table Users contains information about all the users
Table Books contains information about all the books available
Table UserBooks contains information about the books users have and relates the Users and book table together.
To add a book to the user account you just add a record to the UserBooks table.
Table Users
========
Username
UserId <- This is a unique key
name
address
etc...
Table Books
========
BookId
Title
Author
etc...
Table UserBooks
===========
UserId
BookId
To select information about what books a particular user has you would do a query like this:
select U.UserName, B.Title
from Users U, Books B, UserBooks UB
where U.UserId = UB.UserId
and UB.BookId = B.BookId
and U.UserId = 123
To select information about what users have a particular book would do a query like this:
select U.UserName, B.Title
from Users U, Books B, UserBooks UB
where U.UserId = UB.UserId
and UB.BookId = B.BookId
and B.BookId = 'XYZ'
your link between your tables will be some kind of id for the person "owning" these books.
Whether you need a junction table between the two is a matter of design meeting the business rules of the process.
Whether you need a junction table between the two is a matter of design meeting the business rules of the process.
jadedata,
Sure, it depends on the business rules. I was assuming that there would be a reference table of books or that there it would certainly be desirable to have one. As you know this is the standard way to design databases particulariy if the book details are not freeform text or there is the possiblility of a many to many relationship of users to books.
travishaberman,
If you decide to have a Books table and a UserBooks table then you can also add attributes of the UserBook to the UserBook table for example you could add a DatePurchased field to it.
Table UserBooks
===========
UserId
BookId
DatePurchased
Sure, it depends on the business rules. I was assuming that there would be a reference table of books or that there it would certainly be desirable to have one. As you know this is the standard way to design databases particulariy if the book details are not freeform text or there is the possiblility of a many to many relationship of users to books.
travishaberman,
If you decide to have a Books table and a UserBooks table then you can also add attributes of the UserBook to the UserBook table for example you could add a DatePurchased field to it.
Table UserBooks
===========
UserId
BookId
DatePurchased
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That is perfect! thank you,
-TH
-TH
Table relationships can be established in two ways
Through Joins like
SELECT Table1.*, Table2.* LEFT JOIN Table1.PrimaryKey=Tables2.
or where natural relationships don't exist...
SELECT Table1.*, Table2.* WHERE (Table1.PrimaryKey=Tables2
either way accomplishes the same goal,... one will be more correct than the other under various circumstances.
regards
:)-j-