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]

Re: How tough a project is this?

fwells11@hotmai l.com (frank) wrote in message news:<6706f076. 0411260149.4614 1eff@posting.go ogle.com>...[color=blue]
> 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[/color]


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]

Re: How tough a project is this?

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" <fwells11@hotma il.com> wrote in message
news:6706f076.0 411261346.79982 df3@posting.goo gle.com...[color=blue]
> fwells11@hotmai l.com (frank) wrote in message[/color]
news:<6706f076. 0411260149.4614 1eff@posting.go ogle.com>...[color=blue][color=green]
> > 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[/color]
>
>
> 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[/color]

[frank]

Re: How tough a project is this?

"Tom B" <shuckle@hotmai l.com> wrote in message news:<eCDmgPA1E HA.3000@TK2MSFT NGP15.phx.gbl>. ..[color=blue]
> 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
>[/color]
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]

Re: How tough a project is this?

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" <fwells11@hotma il.com> wrote in message
news:6706f076.0 411262348.50838 15c@posting.goo gle.com...[color=blue]
> "Tom B" <shuckle@hotmai l.com> wrote in message[/color]
news:<eCDmgPA1E HA.3000@TK2MSFT NGP15.phx.gbl>. ..[color=blue][color=green]
> > Frank,
> >
> > Just a suggestion or two......
> >
> > Your Tables
> > Rather than having two tables (Category and SubCategory) I'd suggest[/color][/color]
a[color=blue][color=green]
> > single table
> >
> > Category
> > PK ID int
> > Name varchar (100)
> > ParentId int
> >
> > the ParentId would be 0 for a top level category, but otherwise would[/color][/color]
refer[color=blue][color=green]
> > 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[/color][/color]
CategoryId[color=blue][color=green]
> > in the software table. (One Category can have many titles rather than[/color][/color]
one[color=blue][color=green]
> > 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[/color][/color]
software[color=blue][color=green]
> > 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[/color][/color]
Professional"[color=blue][color=green]
> > and the Version 97. But then you've also got Service Releases and[/color][/color]
Service[color=blue][color=green]
> > Packs. Which can make a difference.
> >
> > Not trying to make things more complicated, I just thought I'd mention[/color][/color]
some[color=blue][color=green]
> > difficulties I've had.
> >
> > TomB
> >[/color]
> 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?[/color]