Re: How tough a project is this?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Tom B (shuckle_at_hotmail.com)
Date: 11/26/04 

Date: Fri, 26 Nov 2004 17:04:16 -0500

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

SoftwareCategoryJoin
    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@hotmail.com> wrote in message
news:6706f076.0411261346.79982df3@posting.google.com...
> fwells11@hotmail.com (frank) wrote in message
news:<6706f076.0411260149.46141eff@posting.google.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

Relevant Pages

  • Re: Cascade Delete within Self Referencing Table
    ... your only option is to use an INSTEAD OF trigger. ... CREATE TABLE Table1 (ID INT NOT NULL, ParentID INT REFERENCES Table1(ID), ... Iteration INT) ...
    (microsoft.public.sqlserver.programming)
  • Re: Cascade Delete within Self Referencing Table
    ... > constraints that prevent loops from happening, ... Currently there are no constraints that check for this but in several years ... CREATE TABLE Table1 (ID INT NOT NULL, ParentID INT REFERENCES Table1(ID), ...
    (microsoft.public.sqlserver.programming)
  • Re: splitting the string
    ... > ParentId int, ... > ChildId int, ... > If the separate values within the legacy string all represent instances ...
    (microsoft.public.sqlserver.programming)
  • UDF returns table to feed a call back to itself ... how? (cross post)
    ... varchar, @parentid int) ... if @str is not null ... What i'd like to be able to do is pass in a delimited string such as ...
    (microsoft.public.sqlserver.programming)
  • Re: Expanding Hierarchies
    ... ChildId INT NOT NULL, ... CREATE PROCEDURE expand AS ... "Trees & Hierarchies in SQL" by Joe Celko ... > ParentID ParentName ChildID ChildName ...
    (microsoft.public.sqlserver.programming)