Hi!



I am not sure how much you know about the basics of relational database

design/implementation, but I feel there is lot you need to understand before

you go ahead.



Read my previous posts and think about WHERE you store data.



In tbl_accessory you store data about each Accessory and which subcat it

belongs to.

Since every subcat is related to a cat, you don?t need to store the catID in

this table.



You have to create the tables Category and Subcategory and fill these tables

with valid information. I give you one example (if a subcat has to be

related to a cat):



In tbl_cat



CatID	Catname

1	Engine

2	Lights

3	Chassi



In tbl_subcat

SubcatID	Subcatname	CatID

1		Valves	1

2		Sparkplugs	1

3		Bulbs		2

4		Bumper	3

etc



Then in the form you have fields that insert date in ONE table, ie

tbl_accessory

If you think about it, what happens if the information about which subcat an

accessoire belongs to never gets stored in tbl_accessory? You want this data

to be stored in another table. That way you will never know what what subcat

each and every accessoire belong to, because you never store that

information. There is lot more to be said but please read more about

relational database design and SQL. This is a good start:

http://www.datatexcg.com/

http://sqlcourse.com/

Microsoft link (watch for line wrapping)

http://support.microsoft.com/default.aspx?scid=%2Fservicedesks%2Fwebcasts%2F

wc060600%2Fwcblurb060600%2Easp



Hakan





-----Ursprungligt meddelande-----

Fran: arshad siddiqui [mailto:ash_arshad@y...]

Skickat: den 18 februari 2002 05:57

Till: ASP Databases

Amne: [asp_databases] Re: SV: Re: SV: Re: SV: very important





Hi,

Thanks for your help.

Its a great help for me as I was too much confused for

my database design part.

Can you please tell me how to insert in all the three

tables.Suppose I want to insert

AccessoryId,CategoryID,CategoryName,SubcategoryID,SubcategoryName,Color,weig

ht

etc.because CategoryID and CategoryName is to be

inserted in tbl_Categry,SubCategoryID and

SubCategoryName in tbl_Subcategory,and rest in

tbl_accessory.

I know I am taking too much of your time but your

suggestions are very helpful for me.

Thanks

Regards

--Arshad--









--- Hakan_Frennesson <hakan@c...> wrote:

> Hi!

>

> Well,, this design would perfectly achieve what you

> want:

>

> tbl_category:

> CatID, Catname

>

> tbl_subcategory:

> SubcatID, Subcatname, CatID

>

> tbl_accessory:

> AccID, AccName, SubcatID, Color, Weight, etc

>

> Relate CatID in tbl_category with CatID in

> tbl_subcategory (one-to-many relation)

> and SubcatID in tbl_subcategory with SubcatID in

> tbl_accessory (one-to-many relation)

>

> First you list your categories which are linked.

> Clicking a link takes you to all related

> subcategories

> Clicking a subcategory takes you to all related

> accessoires

>

> I can?t see any other way of doing it. What do you

> mean wouldn?t work with

> this?

>

> Hakan

>

>

>

>

> -----Ursprungligt meddelande-----

> Fran: arshad siddiqui [mailto:ash_arshad@y...]

> Skickat: den 17 februari 2002 06:09

> Till: ASP Databases

> Amne: [asp_databases] Re: SV: Re: SV: very important

>

>

> Hi,

> Thanks for your invaluable suggestion.

> Actually I want to display all the accessories

> related

> to a particular category and subcategory.That is

> fist

> of all the categories shuld be displayed and when

> you

> click on one category all of its sub categories

> should

> come then you can click on any of the subcategory to

> get all the accessories.

> I wanna make an admin page also so that in that I

> enter the category,subcategory and all the details

> of

> accessory and submit it.In that case you need not

> have

> to make entry into the database manually.

> You can get a beeter idea what I want to do by going

> to the following URL:

> http://store.summitracing.com

> I think now you must get the idea about what exactly

> I

> need to know.

> Thanks again

> Regards

> --Arshad-

>

>

=====================================================

> --- Hakan_Frennesson <hakan@c...> wrote:

> > Hi!

> >

> > If a subcat is related to a category you can do

> > this:

> >

> > tbl_category:

> > CatID, Catname

> >

> > tbl_subcategory:

> > SubcatID, Subcatname, CatID

> >

> > tbl_accessory:

> > AccID, AccName, SubcatID, Color, Weight, etc

> >

> > Relate CatID in tbl_category with CatID in

> > tbl_subcategory (one-to-many

> > relation)

> > and SubcatID in tbl_subcategory with SubcatID in

> > tbl_accessory (one-to-many

> > relation)

> >

> > I should really know more about what you want to

> > achieve with your database

> > to design it properly, but my first solution is

> > easiest and will probably do

> > the job for you.

> >

> > The people that fill out the form, are they

> suppose

> > to categorize and

> > subcategorize each item themselves?

> >

> > Taking a lot for granted, I assume they fill in

> what

> > category and

> > subcategory a certain item belong to together with

> > all the information that

> > goes with each item.

> >

> > Am I correct, or is the assumption totally wrong?

> >

> > Hakan

> >

> > -----Ursprungligt meddelande-----

> > Fran: arshad siddiqui

> [mailto:ash_arshad@y...]

> > Skickat: den 16 februari 2002 06:03

> > Till: ASP Databases

> > Amne: [asp_databases] Re: SV: very important

> >

> >

> > Hi Hakan,

> > Thanks for your help.

> > Sorry for the delay as I was indisposed.

> > I didnt got your first question please explain.

> > Subcategory should belong to some category.I think

> > all

> > the three tables should be interlinked but the

> > problem

> > is how?

> > second thing is if I interlink the tables and make

> > relation then how to Upload the things.

> > Thanks

> > Regards

> > --Arshad--

> >

> >

> > --- Hakan_Frennesson <hakan@c...> wrote:

> > > Hi!

> > >

> > > There are a few questions that need to be sorted

> > > out:

> > >

> > > First of all, to whom do I send the bill for

> doing

> > > the design?

> > > Two: Does a SubCategory have to belong to a

> > > Category?

> > >

> > > As far as I can see, you need the 3 tables, but

> > with

> > > some changes:

> > >

> > > Category:

> > > CatID, Catname

> > >

> > >

> > > Subcategory:

> > > SubcatID, Subcatname

> > >

> > >

> > > Accessory:

> > > AccID, AccName, CatID, SubcatID, Color, Weight,

> > etc

> > >

> > >

> > > Then in the form you only insert data to one

> > table.

> > > Only in rare cases you

> > > want to insert data in more then one table. In

> > that

> > > form you have two

> > > dropdown boxes showing the Catnames and

> > Subcatnames

> > > respectively. When form

> > > data is submitted ie an accessory is added to

> the

> > > the table Accessory, you

> > > send the CatID and SubCatID along with the rest

> of

> > > information to the

> > > Accessory table.

> > >

> > > This is a straight forward and simple solution.

> > > However, if one subcategory

> > > has to belong to a Category then the solution is

> a

> > > little more advanced and

> > > I need an answer to my first question ;-)

> > >

> > > Hakan

> > >

> > >

> > >

> > >

> > > -----Ursprungligt meddelande-----

> > > Fran: arshad siddiqui

> > [mailto:ash_arshad@y...]

> > > Skickat: den 14 februari 2002 09:55

> > > Till: ASP Databases

>

=== message truncated ===





__________________________________________________

Do You Yahoo!?

Yahoo! Sports - Coverage of the 2002 Olympic Games

http://sports.yahoo.com






$subst('Email.Unsub').

Hi Hakan,

Thanks for the design.The design is really helpful.I

know you must be tired of my question.actually I have

never worked on the related tables.

I visited the URL'srovided by you but only theoretical

basics are there.

One last question I wanna ask you.Can you please give

me the sql queries for:

1. To display the

Catname,Subcatname,Accessoryname,color,weight,price

where Catname=something and subcatname=something.

2.An insert sql statement to insert into the database.

insert into database

CatId,Catname,Subcatid,subcatname,AccessoryId,Accessoryname,color,weight,price

etc.



These two queries will solve my problem.

Thank you 

Regards

--Arshad--

--- Hakan_Frennesson <hakan@c...> wrote:

> Hi!

> 

> I am not sure how much you know about the basics of

> relational database

> design/implementation, but I feel there is lot you

> need to understand before

> you go ahead.

> 

> Read my previous posts and think about WHERE you

> store data.

> 

> In tbl_accessory you store data about each Accessory

> and which subcat it

> belongs to.

> Since every subcat is related to a cat, you don?t

> need to store the catID in

> this table.

> 

> You have to create the tables Category and

> Subcategory and fill these tables

> with valid information. I give you one example (if a

> subcat has to be

> related to a cat):

> 

> In tbl_cat

> 

> CatID	Catname

> 1	Engine

> 2	Lights

> 3	Chassi

> 

> In tbl_subcat

> SubcatID	Subcatname	CatID

> 1		Valves	1

> 2		Sparkplugs	1

> 3		Bulbs		2

> 4		Bumper	3

> etc

> 

> Then in the form you have fields that insert date in

> ONE table, ie

> tbl_accessory

> If you think about it, what happens if the

> information about which subcat an

> accessoire belongs to never gets stored in

> tbl_accessory? You want this data

> to be stored in another table. That way you will

> never know what what subcat

> each and every accessoire belong to, because you

> never store that

> information. There is lot more to be said but please

> read more about

> relational database design and SQL. This is a good

> start:

> http://www.datatexcg.com/

> http://sqlcourse.com/

> Microsoft link (watch for line wrapping)

>

http://support.microsoft.com/default.aspx?scid=%2Fservicedesks%2Fwebcasts%2F

> wc060600%2Fwcblurb060600%2Easp

> 

> Hakan

> 

> 

> -----Ursprungligt meddelande-----

> Fran: arshad siddiqui [mailto:ash_arshad@y...]

> Skickat: den 18 februari 2002 05:57

> Till: ASP Databases

> Amne: [asp_databases] Re: SV: Re: SV: Re: SV: very

> important

> 

> 

> Hi,

> Thanks for your help.

> Its a great help for me as I was too much confused

> for

> my database design part.

> Can you please tell me how to insert in all the

> three

> tables.Suppose I want to insert

>

AccessoryId,CategoryID,CategoryName,SubcategoryID,SubcategoryName,Color,weig

> ht

> etc.because CategoryID and CategoryName is to be

> inserted in tbl_Categry,SubCategoryID and

> SubCategoryName in tbl_Subcategory,and rest in

> tbl_accessory.

> I know I am taking too much of your time but your

> suggestions are very helpful for me.

> Thanks

> Regards

> --Arshad--

> 

> 

> 

> 

> --- Hakan_Frennesson <hakan@c...> wrote:

> > Hi!

> >

> > Well,, this design would perfectly achieve what

> you

> > want:

> >

> > tbl_category:

> > CatID, Catname

> >

> > tbl_subcategory:

> > SubcatID, Subcatname, CatID

> >

> > tbl_accessory:

> > AccID, AccName, SubcatID, Color, Weight, etc

> >

> > Relate CatID in tbl_category with CatID in

> > tbl_subcategory (one-to-many relation)

> > and SubcatID in tbl_subcategory with SubcatID in

> > tbl_accessory (one-to-many relation)

> >

> > First you list your categories which are linked.

> > Clicking a link takes you to all related

> > subcategories

> > Clicking a subcategory takes you to all related

> > accessoires

> >

> > I can?t see any other way of doing it. What do you

> > mean wouldn?t work with

> > this?

> >

> > Hakan

> >

> >

> >

> >

> > -----Ursprungligt meddelande-----

> > Fran: arshad siddiqui

> [mailto:ash_arshad@y...]

> > Skickat: den 17 februari 2002 06:09

> > Till: ASP Databases

> > Amne: [asp_databases] Re: SV: Re: SV: very

> important

> >

> >

> > Hi,

> > Thanks for your invaluable suggestion.

> > Actually I want to display all the accessories

> > related

> > to a particular category and subcategory.That is

> > fist

> > of all the categories shuld be displayed and when

> > you

> > click on one category all of its sub categories

> > should

> > come then you can click on any of the subcategory

> to

> > get all the accessories.

> > I wanna make an admin page also so that in that I

> > enter the category,subcategory and all the details

> > of

> > accessory and submit it.In that case you need not

> > have

> > to make entry into the database manually.

> > You can get a beeter idea what I want to do by

> going

> > to the following URL:

> > http://store.summitracing.com

> > I think now you must get the idea about what

> exactly

> > I

> > need to know.

> > Thanks again

> > Regards

> > --Arshad-

> >

> >

>

=====================================================

> > --- Hakan_Frennesson <hakan@c...> wrote:

> > > Hi!

> > >

> > > If a subcat is related to a category you can do

> > > this:

> > >

> > > tbl_category:

> > > CatID, Catname

> > >

> > > tbl_subcategory:

> > > SubcatID, Subcatname, CatID

> > >

> > > tbl_accessory:

> > > AccID, AccName, SubcatID, Color, Weight, etc

> > >

> > > Relate CatID in tbl_category with CatID in

> > > tbl_subcategory (one-to-many

> > > relation)

> > > and SubcatID in tbl_subcategory with SubcatID in

> > > tbl_accessory (one-to-many

> > > relation)

> > >

> > > I should really know more about what you want to

> > > achieve with your database

> > > to design it properly, but my first solution is

> > > easiest and will probably do

> > > the job for you.

> > >

> > > The people that fill out the form, are they

> 

=== message truncated ===





__________________________________________________

Do You Yahoo!?

Yahoo! Sports - Coverage of the 2002 Olympic Games

http://sports.yahoo.com