Best way to keep user's product infos

Hi all.

I'm building a database to keep track of users and their products. The number
of users is expected to always increase (hopefully and each user can add any
number of products to their account.

The product definition will have several fields and might take several
kilobytes of space (title, short and long description, etc).

I have a user database that keeps track of all user related information, like
emails, address, etc. What I was puzzling over was how to store the products
for each users. Creating a "products" table for each user was my first thought
but I'm not sure it's the best way to go, especially when thousands of users
will have an account.

Another idea was to create a global "Products" table and place all products for
all users in there with an identifier for doing lookups. Again, I'm not sure
this is the right kind if approach.

Drive space is not an issue, but quick delivery of product infos to viewer is.

Any suggestion?

Thanks a lot,

--Eric
http://www.tlnewsreader.com

Eric Fortier wrote:

> Hi all.
>
> I'm building a database to keep track of users and their products. The number
> of users is expected to always increase (hopefully and each user can add any
> number of products to their account.
>
> The product definition will have several fields and might take several
> kilobytes of space (title, short and long description, etc).
>
> I have a user database that keeps track of all user related information, like
> emails, address, etc. What I was puzzling over was how to store the products
> for each users. Creating a "products" table for each user was my first thought
> but I'm not sure it's the best way to go, especially when thousands of users
> will have an account.
>
> Another idea was to create a global "Products" table and place all products for
> all users in there with an identifier for doing lookups. Again, I'm not sure
> this is the right kind if approach.
>
> Drive space is not an issue, but quick delivery of product infos to viewer is.
>
> Any suggestion?
>
> Thanks a lot,
>
> --Eric
> http://www.tlnewsreader.com
>

I think your second approach makes sense. You would have to tie the
product/s to each user via a PK as in all relational designs. Read up on
relational db design to get a better idea on how to attack the questions
you will have. The MySQL manual is a good place to start. Also,
'Database Design For Mere Mortals' by Michael Hernandez has done me well.

--
Amer Neely, Softouch Information Services
Home of Spam Catcher
W: www.softouch.on.ca
E: trudge@softouch.on.ca
Perl | MySQL | CGI programming for all data entry forms.
"We make web sites work!"

Amer,

Thank you for your reply and the book suggestion! I'll look the book up.

--Eric
http://www.tlnewsreader.com