Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2008
    Posts
    4

    Db Design Recommendations

    I'm designing a .Net application that will be using MS SQL Server 2005 database. I want to design the database structure based on best practices for performance and maintenance. I can best describe the application as an inventory management program for multiple customers. The customer base will initially grow slowly (one to two new customers a week) then probably pick up pace.

    Each customer will start with an inventory list numbering 6,000 to 9,000 items. When adding a new customer I want to replicate the data in the main inventory table and five customer specific supporting tables.

    I have considered two possible paths:


    1. Six Tables - Create and maintain just six tables and identify each record by a customer ID

    or

    2. Individual Table Sets - Create a new set of tables with the customer ID as a prefix to the table name (e.g. "c1234_items, c1234_suppliers, etc.")

    Path 1 I have less tables but the item list table grows by as much as 9,000 records for each customer. After a year's operation that could add up to well over 600,000 records in the item table.

    With Path 2 the item table will remain relatively small so performance wouldn't be affected but I could end up with more than 500 tables. Potentially a management nightmare.

    An alternative would be a hybrid of both paths. Merge the support tables to common tables and create a new item table for each customer. At this point I am leaning in this direction but would appreciate any kind advice from seasoned professionals.
    Last edited by newsomer; 03-19-08 at 23:49.

  2. #2
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Solution #1 is the best practice.

    Imagine you have 3000 customers, in this case you gonna have to manage 3000 items tables instead of just one.

    I don't know much about your company and how you gonna manage your customer/clients.

    But maybe you can use something like a customer class.

    So customer_class1 has certain items and customer_class2 has other items and so on....

    Or maybe you can use item class so the customer may buy items from item_class1 and item_class3 ....


    But anyways if it was me i'll use the path 1.


    Although,

    I don't know how you were planning to affect items to customers but this may be the way I'll do it depending on your situation.

    Item table => pk => id => identity +1
    Customer table => pk => id => identity +1
    item_customer => which links two columns item_id and customer_table

    Hope it can helps you.

    NOTE: I am not a database designing expert.
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  3. #3
    Join Date
    Mar 2008
    Posts
    4
    Thanks for the input Ortho. I think I can safely say that my post indicates my lack of experience. You don't need to be an "expert" to provide good advice.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Who will be the main users for this database? Will it *always* or *almost always* be customers viewing their own data? Would there be much querying of multiple customers' data in a single query?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Don't know enough details to agree or disagree with Ortho about option #1 being the best practice, but I can tell you hands-down that option #2 is a horrible idea.
    Forget you ever though about it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Mar 2008
    Posts
    4
    I'll have to admit it's not really an inventory tracking application but that was the best example that would describe the requirement that I could come up with at the time.
    Quote Originally Posted by pootle flump
    Who will be the main users for this database?
    The list of items will be reviewed and modified initially. After that it would be available for occcasional viewing by the customer.

    Quote Originally Posted by blindman
    option #2 is a horrible idea.
    Forget you ever though about it.
    I kinda thought so. I have no experience dealing with larger databases (>100,000 records/rows) so dealing with a table that could approach and even surpass a million records is a bit daunting.

  7. #7
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Possibly, set up a set of views for each customer, limiting them to just 'their' records...
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  8. #8
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Can you send us your project specifications in a pdf file?

    This way we will be able to support you much better.
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  9. #9
    Join Date
    Mar 2008
    Posts
    4
    Quote Originally Posted by loquin
    Possibly, set up a set of views for each customer, limiting them to just 'their' records...
    Yes, that was my intention if I consolidated the items list into a single table. I was just wondering if this was going to provide reasonable response without having to get into very high end server hardware.

    On further reflection I think I'll be able to go with the consolidated path 1 since there won't be a lot of transactional activity and after a short period the data fetching per customer will become minimal. I'll just write an archiving service to move low activity customer data to a separate table to keep the active table at a reasonable size.

    Thanks to all for the input and thought stimulus.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by newsomer
    Yes, that was my intention if I consolidated the items list into a single table. I was just wondering if this was going to provide reasonable response without having to get into very high end server hardware.
    You could do with getting a handle on indexes. This is why I asked re customers & queries. If you make the customer id the first column in indexes then you will get quick responses. If you will be only returning a very small percentage of the table (less than say three persent) for each request then you only need to really implement this for nonclustered indexes. If you are likely to return more than this percentage of rows then you need to think about covering indexes and the columns to use for your clustered index. Again, I would make the first column the customer id.

    Basically traversing an index is very efficient almost irrespective of the number of rows. A table scan is proportional to the number of rows. So get your indexes right and you should be fine. In effect your data is logically partitioned by customer which is why you should lead most (maybe all) indexes with the customer id.

    Of course if you are a surrogate hugger then this does not apply.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    poots - wouldn't that be "Natural Hugger?" ...
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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