Slightly OT - Database Question

[Andy]

I'm designing a simple database which has 13 fields
I am tempted to use just one table but i know that this is NOT the way to do
things.
I'd like to learn a quick method of good database design.

I have created three tables from my fields as below.
Would this be the correct design?
How should i make the relationships and from which table/field to which?

Hope someone can point me in the right direction.

Cheers in advance
Andy

The fields are:
ID
Category
Category_Image
Category_Text
Product_Title
Product_Description
Product_Image
Product_Price
Show_Product_Price
Retailer_Name
Retailer_Logo
User_Name
User_Password

I have these divided into the three tables as:

CategoryID
Category
Category_Image
Category_Text

ProductID
Product_Title
Product_Description
Product_Image
Product_Price
Show_Product_Price

UserID
Retailer_Name
Retailer_Logo
User_Name
User_Password

[Nancy Gill]

Good database design isn't "quick and easy" but there are tutorials around
and good books on the subject that should help you. What you have below
looks pretty good for three tables called Categories, Products and
Retailers. However .. in order to relate one to another, you will have to
have Foreign Keys implanted. A Foreign Key is an ID from another table that
relates to that particular record and has a column in the current table to
allow for plugging in that record.

For example, in your Products table, you would wish to have CategoryID as a
Foreign Key and the value would be the particular category ID of the
category to which that product belongs. Suppose, for example, you have
defined Category1 to be "Toys". and Product1 is a "ball" but Product2 is a
"vacuum cleaner". Product1 would have Category1 as the Foreign Key in irs
record, but Product2 would have a different CategoryID as foreign key
because it is not a toy.

Make sense?


--
Nancy Gill
Team Macromedia Volunteer for Dreamweaver MX/UltraDev
[url]http://www.macromedia.com/go/team/[/url]
Co-Author: Dreamweaver MX: Instant Troubleshooter (August, 2003)
Technical Editor: DMX 2004: The Complete Reference, DMX 2004: A Beginner's
Guide, Mastering Macromedia Contribute
Technical Reviewer: Dynamic Dreamweaver MX/DMX: Advanced PHP Web Development

"Andy" <AndyjhughesNOSPAM@ntlworld.com> wrote in message
news:cvl2dl$4l3$1@forums.macromedia.com...

> I'm designing a simple database which has 13 fields
> I am tempted to use just one table but i know that this is NOT the way to

do

> things.
> I'd like to learn a quick method of good database design.
>
> I have created three tables from my fields as below.
> Would this be the correct design?
> How should i make the relationships and from which table/field to which?
>
> Hope someone can point me in the right direction.
>
> Cheers in advance
> Andy
>
> The fields are:
> ID
> Category
> Category_Image
> Category_Text
> Product_Title
> Product_Description
> Product_Image
> Product_Price
> Show_Product_Price
> Retailer_Name
> Retailer_Logo
> User_Name
> User_Password
>
> I have these divided into the three tables as:
>
> CategoryID
> Category
> Category_Image
> Category_Text
>
> ProductID
> Product_Title
> Product_Description
> Product_Image
> Product_Price
> Show_Product_Price
>
> UserID
> Retailer_Name
> Retailer_Logo
> User_Name
> User_Password
>
>
>

[Manuel Socarras]

Nancy Gill wrote:

> Good database design isn't "quick and easy" but there are tutorials around
> and good books on the subject that should help you.

i agree with Nancy and suggest you "Database Design for Mere Mortals" by
Michael J. Hernandez

[Michael Fesser]

.oO(Andy)

>I'm designing a simple database which has 13 fields
>I am tempted to use just one table but i know that this is NOT the way to do
>things.

Correct. Putting it all into a single table wastes a lot of resources
becaues of the redundant information and will cause many problems on
insert and update operations (called 'anomalies').

>I'd like to learn a quick method of good database design.

Good DB design can't be done between breakfast and dinner, it definitely
takes some time. Search for some sites/tutorials about DB design,
especially about normalization/normal forms.

>I have created three tables from my fields as below.
>Would this be the correct design?

I would not say a DB design can be 'correct', but it can be good or bad.
At least splitting it like you did below is the correct way.

>How should i make the relationships and from which table/field to which?

To connect two tables there has to be a kind of shared column(s) in both
of them, holding the same values (read up on 'primary keys' and 'foreign
keys'). In your case the products table would need a column categoryID,
which stores the ID of the category the product belongs to. This ID can
then be used to identify the associated record in the categories table.
Such a layout is called 1:m relationship (one-to-many): a product can be
part of only one category, but each category can hold many products.

Another and probably better option is to use a third table to connect
the products and categories table, something like

table products
--------------
productID
title
description
....

table categories
----------------
categoryID
title
description
....

table belongs
-------------
productID
categoryID

This is called a m:n relationship (many-to-many). With this it's
possible to assign multiple categories to one product (and of course to
have multiple products in each category). The products and categories
tables use an ID as their primary key, the same IDs are used in the
third table as foreign keys (references). This table contains a record
for every combination of a product and a category. While such a layout
may look rather complicated, it's very flexible and efficient.

HTH
Micha

[Andy]

Thank you so much to you all. You've helped to make things look a little
clearer.
I'm glad that im at least on the right path :-)

I have found one other question that has me stumped!
Each UserID will have their own pricing sceme for the same products. I can't
get my head round how i could acheive this.
Their would be too many prices for them to simply select one from a dropdown
list, so how could i allow each User (about 250) individual pricing for the
products?

Hope this makes some sense and thanks very much to you all.

Kind Regards
Andy


"Michael Fesser" <netizen@gmx.net> wrote in message
news:mj9s115eg97ljbfke741mm9e29ubut8nro@4ax.com...

> .oO(Andy)
>

>>I'm designing a simple database which has 13 fields
>>I am tempted to use just one table but i know that this is NOT the way to
>>do
>>things.

>
> Correct. Putting it all into a single table wastes a lot of resources
> becaues of the redundant information and will cause many problems on
> insert and update operations (called 'anomalies').
>

>>I'd like to learn a quick method of good database design.

>
> Good DB design can't be done between breakfast and dinner, it definitely
> takes some time. Search for some sites/tutorials about DB design,
> especially about normalization/normal forms.
>

>>I have created three tables from my fields as below.
>>Would this be the correct design?

>
> I would not say a DB design can be 'correct', but it can be good or bad.
> At least splitting it like you did below is the correct way.
>

>>How should i make the relationships and from which table/field to which?

>
> To connect two tables there has to be a kind of shared column(s) in both
> of them, holding the same values (read up on 'primary keys' and 'foreign
> keys'). In your case the products table would need a column categoryID,
> which stores the ID of the category the product belongs to. This ID can
> then be used to identify the associated record in the categories table.
> Such a layout is called 1:m relationship (one-to-many): a product can be
> part of only one category, but each category can hold many products.
>
> Another and probably better option is to use a third table to connect
> the products and categories table, something like
>
> table products
> --------------
> productID
> title
> description
> ...
>
> table categories
> ----------------
> categoryID
> title
> description
> ...
>
> table belongs
> -------------
> productID
> categoryID
>
> This is called a m:n relationship (many-to-many). With this it's
> possible to assign multiple categories to one product (and of course to
> have multiple products in each category). The products and categories
> tables use an ID as their primary key, the same IDs are used in the
> third table as foreign keys (references). This table contains a record
> for every combination of a product and a category. While such a layout
> may look rather complicated, it's very flexible and efficient.
>
> HTH
> Micha
>

[CMBergin]

Set up another table. :)
Leave "price" out of the product table (unless you want it there as a
fallback) and make a new table
UserPrices
-------------
UserID
ProductID
Price

Then, you just have to link the user, product, and userprices tables to get
the appropriate product prices for each user.

"Andy" <AndyjhughesNOSPAM@ntlworld.com> wrote in message
news:cvmq2p$kou$1@forums.macromedia.com...

> Thank you so much to you all. You've helped to make things look a little
> clearer.
> I'm glad that im at least on the right path :-)
>
> I have found one other question that has me stumped!
> Each UserID will have their own pricing sceme for the same products. I

can't

> get my head round how i could acheive this.
> Their would be too many prices for them to simply select one from a

dropdown

> list, so how could i allow each User (about 250) individual pricing for

the

> products?
>
> Hope this makes some sense and thanks very much to you all.
>
> Kind Regards
> Andy
>
>
> "Michael Fesser" <netizen@gmx.net> wrote in message
> news:mj9s115eg97ljbfke741mm9e29ubut8nro@4ax.com...

> > .oO(Andy)
> >

> >>I'm designing a simple database which has 13 fields
> >>I am tempted to use just one table but i know that this is NOT the way

to

> >>do
> >>things.

> >
> > Correct. Putting it all into a single table wastes a lot of resources
> > becaues of the redundant information and will cause many problems on
> > insert and update operations (called 'anomalies').
> >

> >>I'd like to learn a quick method of good database design.

> >
> > Good DB design can't be done between breakfast and dinner, it definitely
> > takes some time. Search for some sites/tutorials about DB design,
> > especially about normalization/normal forms.
> >

> >>I have created three tables from my fields as below.
> >>Would this be the correct design?

> >
> > I would not say a DB design can be 'correct', but it can be good or bad.
> > At least splitting it like you did below is the correct way.
> >

> >>How should i make the relationships and from which table/field to which?

> >
> > To connect two tables there has to be a kind of shared column(s) in both
> > of them, holding the same values (read up on 'primary keys' and 'foreign
> > keys'). In your case the products table would need a column categoryID,
> > which stores the ID of the category the product belongs to. This ID can
> > then be used to identify the associated record in the categories table.
> > Such a layout is called 1:m relationship (one-to-many): a product can be
> > part of only one category, but each category can hold many products.
> >
> > Another and probably better option is to use a third table to connect
> > the products and categories table, something like
> >
> > table products
> > --------------
> > productID
> > title
> > description
> > ...
> >
> > table categories
> > ----------------
> > categoryID
> > title
> > description
> > ...
> >
> > table belongs
> > -------------
> > productID
> > categoryID
> >
> > This is called a m:n relationship (many-to-many). With this it's
> > possible to assign multiple categories to one product (and of course to
> > have multiple products in each category). The products and categories
> > tables use an ID as their primary key, the same IDs are used in the
> > third table as foreign keys (references). This table contains a record
> > for every combination of a product and a category. While such a layout
> > may look rather complicated, it's very flexible and efficient.
> >
> > HTH
> > Micha
> >

>
>
>

[Andy]

Thanks CM

Very much appreciated !

Andy - :-)

"CMBergin" <NoHarvestForYou@NoSpam.org> wrote in message
news:cvn885$c2k$1@forums.macromedia.com...

> Set up another table. :)
> Leave "price" out of the product table (unless you want it there as a
> fallback) and make a new table
> UserPrices
> -------------
> UserID
> ProductID
> Price
>
> Then, you just have to link the user, product, and userprices tables to
> get
> the appropriate product prices for each user.
>
> "Andy" <AndyjhughesNOSPAM@ntlworld.com> wrote in message
> news:cvmq2p$kou$1@forums.macromedia.com...

>> Thank you so much to you all. You've helped to make things look a little
>> clearer.
>> I'm glad that im at least on the right path :-)
>>
>> I have found one other question that has me stumped!
>> Each UserID will have their own pricing sceme for the same products. I

> can't

>> get my head round how i could acheive this.
>> Their would be too many prices for them to simply select one from a

> dropdown

>> list, so how could i allow each User (about 250) individual pricing for

> the

>> products?
>>
>> Hope this makes some sense and thanks very much to you all.
>>
>> Kind Regards
>> Andy
>>
>>
>> "Michael Fesser" <netizen@gmx.net> wrote in message
>> news:mj9s115eg97ljbfke741mm9e29ubut8nro@4ax.com...

>> > .oO(Andy)
>> >
>> >>I'm designing a simple database which has 13 fields
>> >>I am tempted to use just one table but i know that this is NOT the way

> to

>> >>do
>> >>things.
>> >
>> > Correct. Putting it all into a single table wastes a lot of resources
>> > becaues of the redundant information and will cause many problems on
>> > insert and update operations (called 'anomalies').
>> >
>> >>I'd like to learn a quick method of good database design.
>> >
>> > Good DB design can't be done between breakfast and dinner, it
>> > definitely
>> > takes some time. Search for some sites/tutorials about DB design,
>> > especially about normalization/normal forms.
>> >
>> >>I have created three tables from my fields as below.
>> >>Would this be the correct design?
>> >
>> > I would not say a DB design can be 'correct', but it can be good or
>> > bad.
>> > At least splitting it like you did below is the correct way.
>> >
>> >>How should i make the relationships and from which table/field to
>> >>which?
>> >
>> > To connect two tables there has to be a kind of shared column(s) in
>> > both
>> > of them, holding the same values (read up on 'primary keys' and
>> > 'foreign
>> > keys'). In your case the products table would need a column categoryID,
>> > which stores the ID of the category the product belongs to. This ID can
>> > then be used to identify the associated record in the categories table.
>> > Such a layout is called 1:m relationship (one-to-many): a product can
>> > be
>> > part of only one category, but each category can hold many products.
>> >
>> > Another and probably better option is to use a third table to connect
>> > the products and categories table, something like
>> >
>> > table products
>> > --------------
>> > productID
>> > title
>> > description
>> > ...
>> >
>> > table categories
>> > ----------------
>> > categoryID
>> > title
>> > description
>> > ...
>> >
>> > table belongs
>> > -------------
>> > productID
>> > categoryID
>> >
>> > This is called a m:n relationship (many-to-many). With this it's
>> > possible to assign multiple categories to one product (and of course to
>> > have multiple products in each category). The products and categories
>> > tables use an ID as their primary key, the same IDs are used in the
>> > third table as foreign keys (references). This table contains a record
>> > for every combination of a product and a category. While such a layout
>> > may look rather complicated, it's very flexible and efficient.
>> >
>> > HTH
>> > Micha
>> >

>>
>>
>>

>
>
>