How tough a project is this?

[frank]

Hi there. Before anyone gripes about cross posting, Ill say upfront
that I just posted this message to am SQL server newsgroup because I
want feedback from database developers as well as asp coders...

I want to create a web based software rating database. I have a
number of objectives that I would need to achieve to make this a
useable tool and some are not easy figure out considering my
begginning level of db and asp knowledge. I am hoping that with an
asp code generator and some occasional feedback from the talent that
roams this newsgroup, I may be able to accomplish my goal.

Here are the objectives of this tool:

1. Authenticated visitors must to be able to create Sub-Categories
under a drop down list of pre-defined Categories. The drop down list
also needs to be dynamically updated as new Categories are added to
it.

2. Authenticated visitors must be able to add software titles (and
other attributes) under the Sub-Categories.

3. Non authenticated visitors (public) need to be able to search and
browse the list of titles and add a rating to them.

4. The ratings will be on a number of criterion and the actual rating
figures need to be averaged and then displayed as a graphic on the end
of each search result line item etc.

There are tens of thousands of software titles and this tool would be
available to the Internet so this has the potential of becoming a
large database. This makes poorly scaleable MSAccess out of the
question. My next two realistic database choices are MSSQL 2000 or
MySQL. I have a preference for MSSQL 2000 because I am pretty
familiar with it and I already have a environment where I can build my
project.

What I am hoping for out of this post is some feedback as to how
difficult a project this really is. Feedback appreciated.

-Frank Wells

[frank]

fw******@hotmai l.com (frank) wrote in message news:<67******* *************** ****@posting.go ogle.com>...

Hi there. Before anyone gripes about cross posting, Ill say upfront
that I just posted this message to am SQL server newsgroup because I
want feedback from database developers as well as asp coders...

I want to create a web based software rating database. I have a
number of objectives that I would need to achieve to make this a
useable tool and some are not easy figure out considering my
begginning level of db and asp knowledge. I am hoping that with an
asp code generator and some occasional feedback from the talent that
roams this newsgroup, I may be able to accomplish my goal.

Here are the objectives of this tool:

1. Authenticated visitors must to be able to create Sub-Categories
under a drop down list of pre-defined Categories. The drop down list
also needs to be dynamically updated as new Categories are added to
it.

2. Authenticated visitors must be able to add software titles (and
other attributes) under the Sub-Categories.

3. Non authenticated visitors (public) need to be able to search and
browse the list of titles and add a rating to them.

4. The ratings will be on a number of criterion and the actual rating
figures need to be averaged and then displayed as a graphic on the end
of each search result line item etc.

There are tens of thousands of software titles and this tool would be
available to the Internet so this has the potential of becoming a
large database. This makes poorly scaleable MSAccess out of the
question. My next two realistic database choices are MSSQL 2000 or
MySQL. I have a preference for MSSQL 2000 because I am pretty
familiar with it and I already have a environment where I can build my
project.

What I am hoping for out of this post is some feedback as to how
difficult a project this really is. Feedback appreciated.

-Frank Wells

Decided to throw my ideas together to make them easier to convey.
Here is what I have so far. Hopefully my IP address won't change
anytime soom so you can see them...

http://24.24.175.182/rating/ratings.htm

BTW, thank you for responding John

[Tom B]

Frank,

Just a suggestion or two......

Your Tables
Rather than having two tables (Category and SubCategory) I'd suggest a
single table

Category
PK ID int
Name varchar (100)
ParentId int

the ParentId would be 0 for a top level category, but otherwise would refer
to a category id in the save table. This gives you unlimited nesting.

Also, you don't want your SWID in the Category table, you want a CategoryId
in the software table. (One Category can have many titles rather than one
title having multiple categories) If you do need a many-to-many
relationship than you need a third table.
Many to Many

Software
PK ID int
other fields

SoftwareCategor yJoin
PK SoftwareId int
PK CategoryId int

Category
PK ID int
other fields
The last thing, is the Software titles themselves. I've written a software
inventory management tool for my office and it's a pain in the but.

Microsoft Office Professional 97

What's the version - 97? or Professional?

I think I ended up calling the name of the software "Office Professional"
and the Version 97. But then you've also got Service Releases and Service
Packs. Which can make a difference.

Not trying to make things more complicated, I just thought I'd mention some
difficulties I've had.

TomB

"frank" <fw******@hotma il.com> wrote in message
news:67******** *************** ***@posting.goo gle.com...

fw******@hotmai l.com (frank) wrote in message

news:<67******* *************** ****@posting.go ogle.com>...

Hi there. Before anyone gripes about cross posting, Ill say upfront
that I just posted this message to am SQL server newsgroup because I
want feedback from database developers as well as asp coders...

I want to create a web based software rating database. I have a
number of objectives that I would need to achieve to make this a
useable tool and some are not easy figure out considering my
begginning level of db and asp knowledge. I am hoping that with an
asp code generator and some occasional feedback from the talent that
roams this newsgroup, I may be able to accomplish my goal.

Here are the objectives of this tool:

1. Authenticated visitors must to be able to create Sub-Categories
under a drop down list of pre-defined Categories. The drop down list
also needs to be dynamically updated as new Categories are added to
it.

2. Authenticated visitors must be able to add software titles (and
other attributes) under the Sub-Categories.

3. Non authenticated visitors (public) need to be able to search and
browse the list of titles and add a rating to them.

4. The ratings will be on a number of criterion and the actual rating
figures need to be averaged and then displayed as a graphic on the end
of each search result line item etc.

There are tens of thousands of software titles and this tool would be
available to the Internet so this has the potential of becoming a
large database. This makes poorly scaleable MSAccess out of the
question. My next two realistic database choices are MSSQL 2000 or
MySQL. I have a preference for MSSQL 2000 because I am pretty
familiar with it and I already have a environment where I can build my
project.

What I am hoping for out of this post is some feedback as to how
difficult a project this really is. Feedback appreciated.

-Frank Wells

Decided to throw my ideas together to make them easier to convey.
Here is what I have so far. Hopefully my IP address won't change
anytime soom so you can see them...

http://24.24.175.182/rating/ratings.htm

BTW, thank you for responding John

[frank]

"Tom B" <sh*****@hotmai l.com> wrote in message news:<eC******* *******@TK2MSFT NGP15.phx.gbl>. ..

Frank,

Just a suggestion or two......

Your Tables
Rather than having two tables (Category and SubCategory) I'd suggest a
single table

Category
PK ID int
Name varchar (100)
ParentId int

the ParentId would be 0 for a top level category, but otherwise would refer
to a category id in the save table. This gives you unlimited nesting.

Also, you don't want your SWID in the Category table, you want a CategoryId
in the software table. (One Category can have many titles rather than one
title having multiple categories) If you do need a many-to-many
relationship than you need a third table.
Many to Many

Software
PK ID int
other fields

SoftwareCategor yJoin
PK SoftwareId int
PK CategoryId int

Category
PK ID int
other fields
The last thing, is the Software titles themselves. I've written a software
inventory management tool for my office and it's a pain in the but.

Microsoft Office Professional 97

What's the version - 97? or Professional?

I think I ended up calling the name of the software "Office Professional"
and the Version 97. But then you've also got Service Releases and Service
Packs. Which can make a difference.

Not trying to make things more complicated, I just thought I'd mention some
difficulties I've had.

TomB

Thanks for your feedback Tom I appreciate it. You got me looking at
it differently and it now makes a lot more sense. Looking at it from
a web page perspective, I still believe I need a separate SubCategory
Table because I am not sure whether or not a
drop down lookup field can be created from a field on the same table.
I do know it can be done with a field from a different table though.
Also, separating the Category and SubCategory tables negates the need
for a ParentID field in the Category table though doesn't it?

With your suggestions in mind, I added the third column to the
SubCategory table so that it can now reference the Category table
using the CatID. I decided that I wouldn't want to limit a title to
one Sub Category and thus created a many-to-many relationship between
Software and SubCateory using a third table. That table is called
Software_SubCat egory and contains the PK from the Software and
SubCategory tables respectively.

I am now unsure how to define the relationship between the Software
and Ratings tables. I cannot see why a Software Title would need to
have more than one rating, even if the ratings are a multi-part rating
as my ideas suggest. Thoughts?

I updated the diagram with the new relationships and it can still be
seen at the following URL:

http://24.24.175.182/rating/ratings.htm

How do those relationships look now please?

[Tom B]

The bit about the categories (a single table with a relationship to itself)
was for having an unlimited number of subcategories.

If you do it the way you have it(which is fine) then you are limited to a
two level categorization.

You can always change that in the future if necessary. It was just a
suggestion.

I like the addition of the Software_subcat egory table, as I can see that
being needed. Depending on how broad your categories are, many applications
will fit in many categories.

I generally like to add a "Deleted" field to most tables. Rather than
actually deleting records, I'll just mark them deleted. Thus, if there's
ever a need to get that data, it's there.

As far as the relationship between the software and the ratings: I think
what you have is good. I don't see any need to base a rating on a category.
I think the ratings would logically go with the software. You may want to
add a flag to indicate whether the rating is made by a registered user or
not. That way you (your users) can filter, only seeing ratings from
registered users. It's easy to anonymously rate something as crap - or if
you're the author- rate it as awesome.

Another thought, on Amazon, you can view the books rated by a specific user.
The idea being (I assume) that if a particular user appears to have similar
tastes as you, then you can see what they've rated positively as suggestions
for future purchases.

"frank" <fw******@hotma il.com> wrote in message
news:67******** *************** ***@posting.goo gle.com...

"Tom B" <sh*****@hotmai l.com> wrote in message

news:<eC******* *******@TK2MSFT NGP15.phx.gbl>. ..

Frank,

Just a suggestion or two......

Your Tables
Rather than having two tables (Category and SubCategory) I'd suggest a single table

Category
PK ID int
Name varchar (100)
ParentId int

the ParentId would be 0 for a top level category, but otherwise would refer to a category id in the save table. This gives you unlimited nesting.

Also, you don't want your SWID in the Category table, you want a CategoryId in the software table. (One Category can have many titles rather than one title having multiple categories) If you do need a many-to-many
relationship than you need a third table.
Many to Many

Software
PK ID int
other fields

SoftwareCategor yJoin
PK SoftwareId int
PK CategoryId int

Category
PK ID int
other fields
The last thing, is the Software titles themselves. I've written a software inventory management tool for my office and it's a pain in the but.

Microsoft Office Professional 97

What's the version - 97? or Professional?

I think I ended up calling the name of the software "Office Professional" and the Version 97. But then you've also got Service Releases and Service Packs. Which can make a difference.

Not trying to make things more complicated, I just thought I'd mention some difficulties I've had.

TomB

Thanks for your feedback Tom I appreciate it. You got me looking at
it differently and it now makes a lot more sense. Looking at it from
a web page perspective, I still believe I need a separate SubCategory
Table because I am not sure whether or not a
drop down lookup field can be created from a field on the same table.
I do know it can be done with a field from a different table though.
Also, separating the Category and SubCategory tables negates the need
for a ParentID field in the Category table though doesn't it?

With your suggestions in mind, I added the third column to the
SubCategory table so that it can now reference the Category table
using the CatID. I decided that I wouldn't want to limit a title to
one Sub Category and thus created a many-to-many relationship between
Software and SubCateory using a third table. That table is called
Software_SubCat egory and contains the PK from the Software and
SubCategory tables respectively.

I am now unsure how to define the relationship between the Software
and Ratings tables. I cannot see why a Software Title would need to
have more than one rating, even if the ratings are a multi-part rating
as my ideas suggest. Thoughts?

I updated the diagram with the new relationships and it can still be
seen at the following URL:

http://24.24.175.182/rating/ratings.htm

How do those relationships look now please?