Is it ever a good idea to have duplicate tables an

Results 1 to 3 of 3

Thread: Is it ever a good idea to have duplicate tables an

  1. #1
    Michael Bunger Guest

    Default Is it ever a good idea to have duplicate tables an

    1) Is it ever good database design practice (for speed sake, etc.) to essentially make copies of tables to hold a certain group of data? For example, I have come across a database table that stores information for a housing subdivision; ie. lot number, lot size, lot price, etc. And the database to which this table belongs stores this data for many subdivisions. However, instead of having one table that stores the subdivision <BR>information for ALL subdivisions (and having some ID that represents the specific subdivision), this database has one table for each subdivision. For example, &#039Clair Ridge Estates Subdivision Info&#039 and another table &#039Possum Bend Subdivision Info&#039, etc, with each table having the exact same fields. And, if they needed another subdivision, they would make yet another copy and give it a unique name.<BR><BR>2) Suppose I was opening a web site that provided online accounting software to businesses (such as So one day, I may have 3 business subscribing and the next day 2 more may subscribe. Is it best to store all of these customer&#039s information (invoices, customers, employees, accounts payables) together in the same database and tables? Or, is it best to make a database for each of the customers and store this db name so the software would connect to the correct database for a given user?

  2. #2
    Join Date
    Dec 1969

    Default RE: Is it ever a good idea to have duplicate table

    The technique of splitting a single table into multiple tables with the same columns but different rows can be very effective in speeding up performance, particularly if queries are typically going to be run on a certain table and only occasionally run on others. I&#039ve found horizontal partitioning particularly useful when used on servers with limited memory, as large tables are obviously going to consume a lot more than small tables.<BR><BR>The following are a couple of useful links regarding horizontal partitioning. They apply more to SQL Server than other DBMS, but the concepts should still remain appropriate.<BR><BR><BR><BR><BR><BR>As for #2, I would say that it makes more sense to put it all in the same table, although this could depend on what you are storing and how you are accessing it (and how big the tables are expected to get).

  3. #3
    Sid Guest

    Default RE: Is it ever a good idea to have duplicate table

    (1) Generally no (according to 3NF design) - data duplication isn&#039t usually desirable. However, when speed reduction becomes the main concern; denormalizing a database is sometimes the only solution.<BR><BR>(2) I personally would keep everything in one well-designed database. Let your queries and referential integrity be the vehicles to separting specific data. Generally less is more. By that I mean the more you can generalize something (which is more work upfront), the less you have to do later on. From a support standpoint this becomes very apparent.<BR><BR>Good Luck. Hope that helps.

Posting Permissions

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