Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2005
    Posts
    13

    Lightbulb Unanswered: performance question

    Hi,

    What's the best way to store a certain kind of information in an SQL-server database:

    -having one table with many records (e.g. millions)
    or
    -having many tables (e.g. thousands) with less records (e.g. thousands)

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Your question cannot be answered with a simple "statement x is correct" as the answer varies wildly based on the scenario. The best way to find an answer is to do some reading. I suggest you start by reading up on normalisation.
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ah, the ever popular answer: It depends!

    With one pseudo-exception I'll explain in a moment, if you have one single type of thing, then one table is the best way to store it. If you have one thosand similar but slightly different things, then one thousand tables are usually better.

    The one place that I'll make an exception to that rule (which is really just a clarification, not a true exception) is that lookups are a different kind of beast. While people sometimes argue that all lookups belong in a single table (this is known as One True Lookup Table or OTLT), lookups really belong in separate tables.

    If you have 96 kinds of lookup items, those are really 96 quite different things. If you can put hat sizes and truck colors into a lookup table, but you can't exchange a hat size for a truck color (no one I know wears a "Sunburnt Orange" size hat or drives a 6 7/8 colored truck), then the hat sizes are a different thing than the truck colors. Because they are different things, they ought to be in a different table.

    -PatP

Posting Permissions

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