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?

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?

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.

pootle flump ur codings are working excelent.

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.

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.

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

pootle flump ur codings are working excelent.

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.