SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict
    Join Date
    Sep 2008
    Posts
    341
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database design advice

    I am adding to a system that stores details about work completed for their clients.

    There's a lot going on and there's around 60 database tables for various things which have already been developed in one database.

    I now need to add tables for notes made by their staff about a client. The way it works is that about 20 or so notes can be added per week per client. They want to keep this for 5 years and with about 500 clients I work this out at a maximum need of about 2,600,000 records (20 a week * 52 weeks * 5 years * 500 clients).

    There isn't really any way round the number of notes. They have people add these notes on their mobiles / computers at various points throughout the day apparently.

    Also, similar to notes, they need to log number of hours worked and again this can involve about 10 entries per week as different people log hours worked at different times. So, this could easily be well over 1 million records.

    I'm thinking it might be best to have one notes table and one hours table per client. That way the notes table would be 500 times smaller at about 5,000 maximum records.

    There won't be any need to produce reports for all of the notes or hours at one time, so a separate table makes sense. It's not as though we'd ever need to join all 500 client tables to produce reports as it won't be needed.

    As for creating a new table per client then a PHP script would add a new table when they 'add a client' on their back-end system, so it would all be automated.

    So the questions are

    (1) Should I have one notes table for all (2.6m records) or one per client?

    (2) Should I have one hours worked table (over 1m records) for all or one per client?

    (3) Should I create all this in the one database or use a 2nd and/or 3rd database for the extra tables?

    If I create it all in one database then would it be odd that the existing 60 or so tables are in with probably 1,000 other tables that are automatically generated (and probably deleted if a client leaves), or would having 3 databases be the wrong way to go about it?

    Thanks.

  2. #2
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,312
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by johnsmith153 View Post
    ...about 2,600,000 records (20 a week * 52 weeks * 5 years * 500 clients). There isn't really any way round the number of notes.
    That's totally fine. Just make sure you have indexes on any columns used in any SQL clauses -- join columns, sorting columns, and even columns used in a where clause.

    Quote Originally Posted by johnsmith153 View Post
    I'm thinking it might be best to have one notes table and one hours table...
    Yes, that sounds good.

    Quote Originally Posted by johnsmith153 View Post
    ...per client.
    No. Definitely, definitely no. Never do something like that. That would make a cluttered mess that is completely unnecessary. Creating an index will get you the same performance gains without creating 500+ tables.

    Your schema should probably look something like this.

    Code:
    users (presumably your company's staff)
    ----------------------
    | id | whatever_else |
    ----------------------
    
    clients
    -----------------------------
    | id | name | whatever_else |
    -----------------------------
    
    client_notes
    --------------------------------------
    | id | user_id | client_id | content |
    --------------------------------------
    
    hours
    --------------------------------------------------
    | user_id | client_id | reporting_period | hours |
    --------------------------------------------------
    ^                                        ^
    |------------------ PK ------------------|

  3. #3
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    I'm not sure what RDBMS you have there, but it looks like some PARTITION BY is in order.

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by itmitică View Post
    I'm not sure what RDBMS you have there, but it looks like some PARTITION BY is in order.
    why do you need to partition a table with "only" two and a half million records? Properly indexed that isn't troublesome to a database.

  5. #5
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    A partition can act as a standalone table: smaller set of records, to read/write, own indexes, smaller to load than the big whole table ones. This means faster reads/writes while keeping a unified structure.

    2.5m records is big or small depending on the RDBMS and its hosting server capabilities. But usually it means big. I don't think the OP is corporate.


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
  •