Multiple table design problem...

S

sftwrdvlpr

I need to design a database with multiples tables. Here's the scenario. I
have a table that's filled with the names of music CD's. Very similar to
CDDB. I want to have a group of people access my database and select CD's
that they have in their collection from this table. Their list would be
displayed on their computer from this database. They would be able to
catagorize their CD's such as Rock, Pop, Movies, etc.

The information that I need to store would be the user name, the ID of the
CD in the CD database, the location of the CD and whether in a Catagory or
not, the different Catagory names (the names can be duplicated but they need
to be specific to each user), and the location of the Catagories if a
Catagory is inside another Catagory (e.g. Male Singer inside of Rock
Catagory).

So an example of the structure of the output would be:
User1
CD1
CD2
User1Catagory1
CD3
User1Catagory2
CD4
CD5
CD6

User2
CD7
CD1
User1Catagory1
CD4
CD2
and so on...

There would be thousands of users and the tables would be very large.
Obviously, just creating a different table for each of the above would be a
piece of cake, but how to tie it all together in an efficient manner is the
key. I don't think creating a new table for each user is the right way to
do it.

Any ideas?

Thanks,
Fred
 
L

Larry Kaplan

sftwrdvlpr said:
I need to design a database with multiples tables. Here's the scenario. I
have a table that's filled with the names of music CD's. Very similar to
CDDB. I want to have a group of people access my database and select CD's
that they have in their collection from this table. Their list would be
displayed on their computer from this database. They would be able to
catagorize their CD's such as Rock, Pop, Movies, etc.

The information that I need to store would be the user name, the ID of the
CD in the CD database, the location of the CD and whether in a Catagory or
not, the different Catagory names (the names can be duplicated but they need
to be specific to each user), and the location of the Catagories if a
Catagory is inside another Catagory (e.g. Male Singer inside of Rock
Catagory).

So an example of the structure of the output would be:
User1
CD1
CD2
User1Catagory1
CD3
User1Catagory2
CD4
CD5
CD6

User2
CD7
CD1
User1Catagory1
CD4
CD2
and so on...

There would be thousands of users and the tables would be very large.
Obviously, just creating a different table for each of the above would be a
piece of cake, but how to tie it all together in an efficient manner is the
key. I don't think creating a new table for each user is the right way to
do it.

Any ideas?

Thanks,
Fred
http://www.mvps.org/access/

HTH
Larry Kaplan MVP

This posting is provided "AS IS" with
no warranties, and confers no rights.
 
L

Larry Daugherty

Fred,

Diane's advice is good and very relevant and Larry Linson's questions are
relevant to the ambitious project you've sketched. Once you've taken
Diane's advice you'll better understand what a relational database
management system is and may then understand how relevant Larry L's
questions are. It makes a whole bunch of difference whether you plan to
have a few users accessing your desktop database via dial-up access or to
have thousands of concurrent users on a SQL server hosted on the net.

By the way, Larry. I caught one of your regular dog and pony shows in the
Microsoft building in Dallas a couple of years ago. I went up to say "Hi"
after your presentation but you were busy talking to a friend and I had to
go.

Fred, if you're serious about learning how to use Access to solve problems
and provide services you'll find these newsgroups invaluable. I recommend
that you subscribe to a bunch of them and lurk to learn. Type Access in the
newsgroup textbox and it will blow you away.

hth
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top