Lets say i have a database where users can upload their dvd collection.
I want to be able to do a query where i can select all the users who
have a certain DVD. Can someone point me in the right direction as how
to optimally do this?
On Sun, 06 Aug 2006 07:24:08 -0700, ryan wrote:
> Lets say i have a database where users can upload their dvd collection.
> I want to be able to do a query where i can select all the users who
> have a certain DVD. Can someone point me in the right direction as how
> to optimally do this?
Why not:
SELECT user-name FROM dvd-table WHERE dvd-name =
Based on the nature of your question I believe you are a "newbie" and I
suggest that you do some research about Relational Database Management
Systems. A good place to start is to ask the instructor at one of the
local colleges the name of the text they use for their basic database
management course.
If your local college doesn't offer such a course you can use the book I
used when I taught this course last year. Database Systems: Design,
Implementation, & Management by Peter Rob and Carlos Coronel. The
publisher is Thomson Course Technology. If you can't find it locally
it is available from Amazon.
HTH
Jerry
Maybe i didnt state my question clear enough. This is a hypothetical
situation, but what if i had thousands of users? Would i be forced to
store the user IDs of each person who owns that DVD in each DVD row?
This doesnt seem practical to me. I want the DVD table to act as a
library with length, year, etc, and i dont want users to be included in
it.
On Sun, 06 Aug 2006 14:45:13 -0700, ryan wrote:
> Maybe i didnt state my question clear enough. This is a hypothetical
> situation, but what if i had thousands of users? Would i be forced to
> store the user IDs of each person who owns that DVD in each DVD row?
> This doesnt seem practical to me. I want the DVD table to act as a
> library with length, year, etc, and i dont want users to be included in
> it.
Ah!
What you need is a third table. The table will consist of one row for
each DVD owned by each user. Each row will contain two columns, the first
being the primary key from the user table and the second being the primary
key from the DVD table. So if user a has 10 DVDs there will be 10 rows
for user x in this table.
You can wind up with a lot of rows in this table, but each row doesn't
take up very much space and, these days, disk space is very inexpensive.
HTH
Jerry