SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru sowen's Avatar
    Join Date
    Feb 2001
    Location
    Stockport, UK
    Posts
    729
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database design and SQL help if possible

    I am at the start of a new project that will eventually use .aspx with an MS SQL Database. I have put together an Access file to develop the structure.

    The finished site will only display information, nobody but me will be altering the DB, so I am concentrating on the select queries first which I thought would be easier.

    Specs:
    The database will hold around 30000 client records.
    About 10% of those will be premium clients (I hope!).
    There will be around 20 categories.
    There will be around 10 CCTypes (credit/charge cards).
    Each ordinary client will have zero or many categories.
    Each premium client will have at least 1 or many categories.
    Each client, ordinary or premium, will have zero or many CCTypes.

    So after much searching and help file reading I've come up with a database design I think is close to correct.

    It's the first time I have dealt with many-many relationships and the SQL to produce results that could be formatted as below is a bit beyond me,

    particularly when you consider the following:

    The search result should list all premium clients first and in random order i.e. each time the search is run any premium client has a chance to be listed first within the subset, irrespective of name, postcode whatever.

    The data will need 'paging' as most searches will return between 100 and 500 records.

    The data will be 'cleaned' regularly involving deletion of clients and addition of others (I believe this can cause problems with paging data?)

    So my questions (eventually)
    1) Is the Database layout right or could it be done a better /different/more efficient way?
    2) Can anyone point me in the direction of some good SQL tutorials that cover the above?
    3) Can I do the randomising of the records in the SQL query or is it better to programmatically at display.
    4) Should I just hire a programmer to write me some stored procedures

    Any help is much appreciated.
    Cheers

    Heres a link while the images are approved.
    Database

  2. #2
    SitePoint Zealot
    Join Date
    Feb 2002
    Location
    UK
    Posts
    146
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hey, you've probably found these already but anyway...

    paging in asp.net
    http://www.codeproject.com/aspnet/dspaging.asp

    returning rows in random order
    http://www.sqlteam.com/item.asp?ItemID=217

  3. #3
    What? Maelstrom's Avatar
    Join Date
    Oct 2001
    Location
    Whistler BC originally from Guelph Ontario
    Posts
    2,175
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Since I use mysql I can't really help with a couple of your answers. But your layout seems fine. You won't have redundancy and the db should allow for extensibility.

    As for question 2. I know in mysql you can do it with some built in function. And it would be faster for mysql to randomize the results than php (for example). But you really need to talk to someone with more experience in mssql

    Sorry I couldn't help more.
    Maelstrom Personal - Apparition Visions
    Development - PhP || Mysql || Zend || Devshed
    Unix - FreeBSD || FreeBsdForums || Man Pages
    They made me a sitepoint Mentor - Feel free to PM me or Email me and I will see if I can help.

  4. #4
    SitePoint Enthusiast
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    1) Already covered I believe :-)

    2) Signup to mailing lists (ASPSQLServer7 for example) on www.aspfriends.com). Be careful of list rules.. watch for a bit before posting your own questions.

    www.swynk.com is a great SQL Server resource.

    3) There is a great little trick in SQL Server for randomising the records returned. I have it in my other inbox somewhere. I have it in my other inbox somewhere, so if you want me to dig it out, PM me. Randomising in SQL Server will be far superior to doing it in ASP.

    4) Should I just hire a programmer to write me some stored procedures

    Nope... beg nice ones to help you instead ;-)

    SQL Server paging is a doddle. The only question is getting it right for your server architecture. The following stored proc will work very well where your database server and webserver are different machines. You supply a page number, number of records per page to show, and the parameter for your query, and recieve your results back, and parameter @RecordsFound which is the TOTAL number of records found using your query:

    CREATE PROCEDURE dbo.usp_Something_With_Paging (

    @RecordsFound int output, -- results returned
    @PageNum int, -- page number
    @RecsPerPage int, -- number of records to return at a time
    @Description varchar(100)

    )

    AS

    BEGIN

    SET NOCOUNT ON

    -- create a temp table and set an autoincrementing field. only need that field plus one to store the record ID's of records matching our query
    CREATE TABLE #temp(TempID int IDENTITY, RecID int)

    INSERT INTO #temp (RecID) SELECT
    RecID FROM dbo.tblMyTable WHERE tblMyTable.Description = @Description

    -- get the last ID inserted so we know how many rows are being returned, for help with displaying page numbers/links in the ASP

    -- note that randomising these records will NOT be a problem, no matter what you've heard, as TempID is now consecutive
    SELECT @RecordsFound = @@IDENTITY

    -- do the paging
    DECLARE @FirstRec int, @LastRec int
    SELECT @LastRec = @PageNum * @RecsPerPage
    SELECT @FirstRec = (@RecsPerPage * (@PageNum - 1)) + 1

    SELECT *
    FROM #temp INNER JOIN dbo.tblMyTable ON #temp.RecID = tblMyTable.RecID
    WHERE (#Counts.[ID] >= @FirstRec) AND (#Counts.[ID] <= @LastRec)

    SET NOCOUNT OFF

    END


    HTH

  5. #5
    SitePoint Guru sowen's Avatar
    Join Date
    Feb 2001
    Location
    Stockport, UK
    Posts
    729
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the help and assistance guys, I'm checking out all the links.

    mulletboy2 I am praticing my best begging voice now, I'm going to need it

    Cheers again
    Simon

  6. #6
    SitePoint Enthusiast
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sowen - sorry, I've just spotted a typo in that stored procedure code. I copied and pasted it, then forgot to do a bit :-)

    The last query in the sproc should be:

    SELECT *
    FROM #temp INNER JOIN dbo.tblMyTable ON #temp.RecID = tblMyTable.RecID
    WHERE (#temp.[ID] >= @FirstRec) AND (#temp.[ID] <= @LastRec)

    Any luck with the randomising results yet? I haven't managed to find the email I was referring to.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •