Results 1 to 8 of 8
  1. #1
    Join Date
    May 2007
    Posts
    3

    Question When to create separate databases for customers versus extra columns

    I have a database that manages our customers, services, IPs, servers, etc. I'm now making the database usable for other hosting companies as well, not just us. But I'm noticing that almost every table has an ISP_Id. There's not much data that all ISPs share. It wouldn't be a problem for me to simply create completely separate databases for ISPs. Or even easier would be to just create separate tables with the ISP name appended or prepended to the table name.

    What tradeoffs are there? Is there a performance tradeoff for an application to access many different databases, many different tables or extra ISP_Id identifiers in almost every table? Anything I should consider in managing this data?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by scottmueller
    ...It wouldn't be a problem for me to simply create completely separate databases for ISPs. Or even easier would be to just create separate tables with the ISP name appended or prepended to the table name.
    How about "None of the above", and instead you add a column to your database called ISP, allowing you to manage all your accounts with one database and one schema.
    Its called Normalization, and is one of the most basic principles of database design.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    May 2007
    Posts
    3
    Quote Originally Posted by blindman
    How about "None of the above", and instead you add a column to your database called ISP, allowing you to manage all your accounts with one database and one schema.
    Its called Normalization, and is one of the most basic principles of database design.
    By adding a column to my database I assume you mean tables. That's actually how I have it right now as I stated above. Was wondering if it makes more sense or if there's a good reason to instead split ISPs into separate tables or databases.

    This isn't normalization. Normalization or not, some of these tables will get extremely large. I'm not a DBA at all, but I understand that extremely large tables can really consume resources. These tables can be magnitudes smaller if I split ISPs into tables or databases. Aside from performance, since the ISPs don't share anything and in fact it would be a disaster if ISP data was shared accidentally, is it a better design to keep ISP data more separate?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by scottmueller
    What tradeoffs are there?
    The tradeoff is a maintenance nightmare and a sudden acceleration in your requirements to write unweildy, lengthy, inflexible code.

    You are not the first person to reach this juncture and think "ah... what if I....". Blindman is right though - promise.

    Try extending your logic to another senario. You have worked for a long time thinking of this as a database for a single ISP so it is worth abstracting your problem a little. What if you were tracking a school. Would you consider one database per student or one version of each of your tables per student? The principle is the same.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    May 2007
    Posts
    3
    Thank you pootle flump. Good analogy. Though I'm not tracking a school (or in my situation, I'm not tracking the world of hosting). In your analogy, I'd just be tracking individual students and nothing outside of them individually. My database might be called life_of_a_student. The think_about_sex table contains about 10 million records for every student on average. So with 5,000 students I'm tracking, that table balloons to 50 BILLION records.

    However, now I feel more comfortable with my initial decision to simply add an ISP_Id column to almost all tables with the 2 of you above thinking that's definitely the way to go.

  6. #6
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    I think what you are trying to achieve is a parent ISP table associated with several child tables (see image) with different attribute sets. In that case you are abiding by normalization.
    Attached Thumbnails Attached Thumbnails ISP.JPG  

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by scottmueller
    My database might be called life_of_a_student. The think_about_sex table contains about 10 million records for every student on average. So with 5,000 students I'm tracking, that table balloons to 50 BILLION records.
    Remember though that the logical design of your database doesn't really have anything to do with the number of rows. That will be a physical design consideration of course.

    I'm not sure the analogy does break down (I don't quite get your point ) but I think you have gone the right way.

    And hello Certus - nice to see you again
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    Hi pootle, nice to see you, too.

    I was thinking about this issue a bit more and what we may have is an associative relationship. For example:

    Student
    ---------
    01 Jim
    02 Jane
    03 Dick

    Thought
    --------
    01 sex
    02 food
    03 beer

    Student Thought Time
    ----------------------
    01 03 00:01am
    03 03 05:04am
    02 01 07:06pm

    I suggest this because the "billion rows" may be inescapable, however normalization would reduce the size of the "transaction" table.

Posting Permissions

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