DB Design for a software site.

[longwolf]

I'm working toward making a web site to sell some apps I've written.
It will run VBScript with an Access DB.

I'm trying to decide on a DB layout, how does this sound?

tblCustomer
    CustomerID 'auto numbered
    First Name
    Last Name
    Address    'Optional?
    Email      'Required
    
tblPrograms
    ProgramID    'auto numbered
    ProgramName
    ProgramVersion
    FreeUpgradeFromVersionX

tblOrders
    OrderID         'auto numbered
    CustomerID
    ProgramID
    ProgramVersion 
    Date            'Order placed 
    PayPalSaleID
    RegKey           'Users registartion key, could just use the OrderID?
    DownloadCode     'The ASP extention to create their download page
    DownloadAccessed 'Date-time when the download code is first used

tblSupport
    OrderID 'or RegKey?
    SupportRequestEmailAddress 'the email address the customer used when asking for support
    Issue
    Action
    Result

[szlamany]

ProgramVersion is stored in the tblPrograms - it is redundant to store it again in the tblOrders.

Do you need to relate prior versions of programs to each other? If so then add a PriorVersionProgramId to the tblPrograms.

[longwolf]

I was only going to have one row for each program in tblPrograms and list the Current Version in the ProgramVersion field.

But you may have a point.
If I make a new row for each update I could add a list of changes.
That would make it easy to create an ASP page to show the customers the bug fixes and enhancements to the newest version.

I've also added TicketID and Date fields to tblSupport.

When would the PriorVersionProgramId field be useful?

[szlamany]

When would the PriorVersionProgramId field be useful?

If the program name stays the same from version to version it would not be needed.

Otherwise it was simply a way to point a particular version of the program back to the programid of the prior version. Would allow you to make a "list" of just a specific program with all the versions.