By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,824 Members | 1,252 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,824 IT Pros & Developers. It's quick & easy.

Database design/ query question

P: n/a
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?

Aug 6 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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 = <search value>.

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

Aug 6 '06 #2

P: n/a
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.

Aug 6 '06 #3

P: n/a
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
Aug 6 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.