Database architecture question: 1 table per customer or 1 unique table for all customers
up vote
0
down vote
favorite
We have a need to know which database architecture makes more sense to use and why.
We have a list of customers who are all going to use the same table structure (with very few exceptions).
We would have about 10 thousand customers who might all have all about 50 thousand products each.
The processing on products may not be the same for each customer and we would also want to provide a plan where customers could have API access to their data.
Our customers do sell products and their SQL table structure would all have columns such as :
- Feed_ID
- Product_ID
- Product_Description
- Price
- Weight
- etc...
The Feed_ID is used to differentiate the origin of these products and will be unique for each customer - of course.
The 3 choices of relational table structure that we have thought about:
Each customer has its own database and in that database, he has 1 table per product-feed
All customers are hosted under 1 unique database under which all customers all have 1 table per feed - in that case, 1 customer can have 2 tables if he as 2 different product feed.
All customers are hosted under 1 unique database, HOWEVER, in this 3rd solution, we only have 1 unique table that host all products feed of all customers.
Which solution would you use and why you think that the solution you selected is better?
Thank you.
sql database-design product
edited Nov 11 at 8:30
marc_s
566k12610931245
asked Nov 11 at 5:19
MMEL
357
add a comment |
up vote
0
down vote
favorite
We have a need to know which database architecture makes more sense to use and why.
We have a list of customers who are all going to use the same table structure (with very few exceptions).
We would have about 10 thousand customers who might all have all about 50 thousand products each.
The processing on products may not be the same for each customer and we would also want to provide a plan where customers could have API access to their data.
Our customers do sell products and their SQL table structure would all have columns such as :
- Feed_ID
- Product_ID
- Product_Description
- Price
- Weight
- etc...
The Feed_ID is used to differentiate the origin of these products and will be unique for each customer - of course.
The 3 choices of relational table structure that we have thought about:
Each customer has its own database and in that database, he has 1 table per product-feed
All customers are hosted under 1 unique database under which all customers all have 1 table per feed - in that case, 1 customer can have 2 tables if he as 2 different product feed.
All customers are hosted under 1 unique database, HOWEVER, in this 3rd solution, we only have 1 unique table that host all products feed of all customers.
Which solution would you use and why you think that the solution you selected is better?
Thank you.
sql database-design product
edited Nov 11 at 8:30
marc_s
566k12610931245
asked Nov 11 at 5:19
MMEL
357
5
Option #3 is the only one which makes sense and is remotely scalable. Don't use the first two options.
– Tim Biegeleisen
Nov 11 at 5:22
2
There is no good or bad answer. It all depends on your requirements. What is the number of customers, what is their lifecycle (will they be added or not), will you have features same for all customers always, how will you manage bug fixes for each customer etc. etc. While @TimBiegeleisen, is pointed for option #3 as default, if it is multi-tenant setup for example, where customers should have access to their data only, you may choose option #2 as well. This will make management of your customers independent from each other for example.
– muradm
Nov 11 at 5:28
Thank you both for your comments. We would have 10 thousand customers who might all have all 50 thousand products each. 1 huge SQL table as in solution 3 is attractive, however always accessing the same table for any customer may bring some performance issues, also if for whatever reason that table gets corrupted then all customers lose their access. Also, If that unique SQL table gets hacked, the hacker has access to all customers data with that 1 table. Even then I am still not sure which solution is best?
– MMEL
Nov 11 at 5:37
Hi. This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on Minimal, Complete, and Verifiable example. PS Please clarify via post edits, not commments.
– philipxy
Nov 11 at 7:58
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
We have a need to know which database architecture makes more sense to use and why.
We have a list of customers who are all going to use the same table structure (with very few exceptions).
We would have about 10 thousand customers who might all have all about 50 thousand products each.
The processing on products may not be the same for each customer and we would also want to provide a plan where customers could have API access to their data.
Our customers do sell products and their SQL table structure would all have columns such as :
- Feed_ID
- Product_ID
- Product_Description
- Price
- Weight
- etc...
The Feed_ID is used to differentiate the origin of these products and will be unique for each customer - of course.
The 3 choices of relational table structure that we have thought about:
Each customer has its own database and in that database, he has 1 table per product-feed
All customers are hosted under 1 unique database under which all customers all have 1 table per feed - in that case, 1 customer can have 2 tables if he as 2 different product feed.
All customers are hosted under 1 unique database, HOWEVER, in this 3rd solution, we only have 1 unique table that host all products feed of all customers.
Which solution would you use and why you think that the solution you selected is better?
Thank you.
sql database-design product
edited Nov 11 at 8:30
marc_s
566k12610931245
asked Nov 11 at 5:19
MMEL
357
We have a need to know which database architecture makes more sense to use and why.
We have a list of customers who are all going to use the same table structure (with very few exceptions).
We would have about 10 thousand customers who might all have all about 50 thousand products each.
The processing on products may not be the same for each customer and we would also want to provide a plan where customers could have API access to their data.
Our customers do sell products and their SQL table structure would all have columns such as :
- Feed_ID
- Product_ID
- Product_Description
- Price
- Weight
- etc...
The Feed_ID is used to differentiate the origin of these products and will be unique for each customer - of course.
The 3 choices of relational table structure that we have thought about:
Each customer has its own database and in that database, he has 1 table per product-feed
All customers are hosted under 1 unique database under which all customers all have 1 table per feed - in that case, 1 customer can have 2 tables if he as 2 different product feed.
All customers are hosted under 1 unique database, HOWEVER, in this 3rd solution, we only have 1 unique table that host all products feed of all customers.
Which solution would you use and why you think that the solution you selected is better?
Thank you.
sql database-design product
sql database-design product
edited Nov 11 at 8:30
marc_s
566k12610931245
asked Nov 11 at 5:19
MMEL
357
edited Nov 11 at 8:30
marc_s
566k12610931245
asked Nov 11 at 5:19
MMEL
357
edited Nov 11 at 8:30
marc_s
566k12610931245
edited Nov 11 at 8:30
marc_s
566k12610931245
edited Nov 11 at 8:30
marc_s
566k12610931245
566k12610931245
asked Nov 11 at 5:19
MMEL
357
asked Nov 11 at 5:19
MMEL
357
asked Nov 11 at 5:19
MMEL
357
357
5
Option #3 is the only one which makes sense and is remotely scalable. Don't use the first two options.
– Tim Biegeleisen
Nov 11 at 5:22
2
There is no good or bad answer. It all depends on your requirements. What is the number of customers, what is their lifecycle (will they be added or not), will you have features same for all customers always, how will you manage bug fixes for each customer etc. etc. While @TimBiegeleisen, is pointed for option #3 as default, if it is multi-tenant setup for example, where customers should have access to their data only, you may choose option #2 as well. This will make management of your customers independent from each other for example.
– muradm
Nov 11 at 5:28
Thank you both for your comments. We would have 10 thousand customers who might all have all 50 thousand products each. 1 huge SQL table as in solution 3 is attractive, however always accessing the same table for any customer may bring some performance issues, also if for whatever reason that table gets corrupted then all customers lose their access. Also, If that unique SQL table gets hacked, the hacker has access to all customers data with that 1 table. Even then I am still not sure which solution is best?
– MMEL
Nov 11 at 5:37
Hi. This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on Minimal, Complete, and Verifiable example. PS Please clarify via post edits, not commments.
– philipxy
Nov 11 at 7:58
add a comment |
5
Option #3 is the only one which makes sense and is remotely scalable. Don't use the first two options.
– Tim Biegeleisen
Nov 11 at 5:22
2
There is no good or bad answer. It all depends on your requirements. What is the number of customers, what is their lifecycle (will they be added or not), will you have features same for all customers always, how will you manage bug fixes for each customer etc. etc. While @TimBiegeleisen, is pointed for option #3 as default, if it is multi-tenant setup for example, where customers should have access to their data only, you may choose option #2 as well. This will make management of your customers independent from each other for example.
– muradm
Nov 11 at 5:28
Thank you both for your comments. We would have 10 thousand customers who might all have all 50 thousand products each. 1 huge SQL table as in solution 3 is attractive, however always accessing the same table for any customer may bring some performance issues, also if for whatever reason that table gets corrupted then all customers lose their access. Also, If that unique SQL table gets hacked, the hacker has access to all customers data with that 1 table. Even then I am still not sure which solution is best?
– MMEL
Nov 11 at 5:37
Hi. This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on Minimal, Complete, and Verifiable example. PS Please clarify via post edits, not commments.
– philipxy
Nov 11 at 7:58
5
5
Option #3 is the only one which makes sense and is remotely scalable. Don't use the first two options.
– Tim Biegeleisen
Nov 11 at 5:22
Option #3 is the only one which makes sense and is remotely scalable. Don't use the first two options.
– Tim Biegeleisen
Nov 11 at 5:22
2
2
There is no good or bad answer. It all depends on your requirements. What is the number of customers, what is their lifecycle (will they be added or not), will you have features same for all customers always, how will you manage bug fixes for each customer etc. etc. While @TimBiegeleisen, is pointed for option #3 as default, if it is multi-tenant setup for example, where customers should have access to their data only, you may choose option #2 as well. This will make management of your customers independent from each other for example.
– muradm
Nov 11 at 5:28
There is no good or bad answer. It all depends on your requirements. What is the number of customers, what is their lifecycle (will they be added or not), will you have features same for all customers always, how will you manage bug fixes for each customer etc. etc. While @TimBiegeleisen, is pointed for option #3 as default, if it is multi-tenant setup for example, where customers should have access to their data only, you may choose option #2 as well. This will make management of your customers independent from each other for example.
– muradm
Nov 11 at 5:28
Thank you both for your comments. We would have 10 thousand customers who might all have all 50 thousand products each. 1 huge SQL table as in solution 3 is attractive, however always accessing the same table for any customer may bring some performance issues, also if for whatever reason that table gets corrupted then all customers lose their access. Also, If that unique SQL table gets hacked, the hacker has access to all customers data with that 1 table. Even then I am still not sure which solution is best?
– MMEL
Nov 11 at 5:37
Thank you both for your comments. We would have 10 thousand customers who might all have all 50 thousand products each. 1 huge SQL table as in solution 3 is attractive, however always accessing the same table for any customer may bring some performance issues, also if for whatever reason that table gets corrupted then all customers lose their access. Also, If that unique SQL table gets hacked, the hacker has access to all customers data with that 1 table. Even then I am still not sure which solution is best?
– MMEL
Nov 11 at 5:37
Hi. This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on Minimal, Complete, and Verifiable example. PS Please clarify via post edits, not commments.
– philipxy
Nov 11 at 7:58
Hi. This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on Minimal, Complete, and Verifiable example. PS Please clarify via post edits, not commments.
– philipxy
Nov 11 at 7:58
add a comment |
2 Answers
2
active
oldest
votes
up vote
2
down vote
You haven't quite provided enough information. Under almost all circumstances (see below for exceptions), you want one set of tables for all customers. Here are some reasons:
- Performance. A proliferation of tables means the data is spread through more data pages, so you have lots of partially filled data pages. The database is bigger and processing is slower.
- Coding efficiency. If the tables for a customer all have different names, then all the code is dynamic SQL. That is harder to maintain.
- Maintenance. Adding a column or index is very arduous when there are zillions of similar tables.
- Analytics. When similar data is spread through tables, it is really hard to answer questions such as "Which client has the most products?".
- Security. Granting access permissions on a single set of tables is less error prone than on zillions of tables.
And no doubt, I've missed a few reasons. You can see that it is almost a no-brainer to have a single database with a small number of tables.
There are situations where separate databases might be called for. I cannot think of a good reason to have separate tables for each client in a single database.
The number one reason would be security and isolation. There might be a business or even legal reason for storing data into "physically" separate databases, to further minimize the possibility of one client seeing another client's data (accidentally or through hacking).
Another reason would be if clients had bespoke solutions. That is, there are per-client customizations. I would still be inclined to try to put this into a single database solution, but that might not be possible.
Related to this would be an application that you intend to support both in the cloud and on premises. In that case, separate databases per client would probably simplify the application design.
But, in general, you would store the data in a pretty normalized single database, with one table per entity.
answered Nov 11 at 12:15
Gordon Linoff
746k33285390
I agree we did not provide enough details in our question. I love you reply and I feel/agree that the 1 table-for-all approach is very attractive. However: Is accessing 1 big table really providing better performance than 10K tables? Security? With 1 table u would need it set at the record level, wouldn't that be more challenging? About "dynamic SQL", would U really hardcode the name of that table in all your code? Updating a column name or its type across 10K tables would be a real nightmare, yes I agree. Any advantages to using the 1 table-per-customer approach?
– MMEL
Nov 14 at 7:39
add a comment |
up vote
1
down vote
I think having separate tables (or ideally schemas) for each customer is not that bad idea. In addition to benefits you mentioned, this way you can scale your database easily, and you can give customers full control of their data if they want to.
Regarding the drawbacks:
- Managing it is more complicated but not as bad either - you can write
a script to create columns/tables/indexes/etc. You
don't have to do it manually. - It will be a challenge to perform analytics on 10K tables,
although it's not the best idea to mix it with production anyway.
I'd create a separate database (or server) for analytics, running
some overnight job to update reporting tables.
Also, if your table is going to have hundreds of millions rows (10Kx50k?), it's a good idea to split it into smaller pieces, regardless which option you'll choose. If not by customer, then by region or some other bigger group (assuming you are building on premises RDBMS)
answered Nov 14 at 3:33
Mike Twc
56129
1
"if your table is going to have hundreds of millions rows (10Kx50k?), it's a good idea to split it into smaller pieces". This is just simply false and displays a complete lack of understanding of databases and how they scale.
– Gordon Linoff
Nov 14 at 13:21
In my opinion, smaller tables size would NOT improve performance as no hard-disk driver can access 2 different HD space at the exact same time. Performance would be improved if these "split" tables are placed on a completely different server. Again, if the file is too big, it means too many customers using the same file, it means too many requests at the same time, so yes to splitting but then move that other file to a different server.
– MMEL
Nov 14 at 18:59
add a comment |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
You haven't quite provided enough information. Under almost all circumstances (see below for exceptions), you want one set of tables for all customers. Here are some reasons:
- Performance. A proliferation of tables means the data is spread through more data pages, so you have lots of partially filled data pages. The database is bigger and processing is slower.
- Coding efficiency. If the tables for a customer all have different names, then all the code is dynamic SQL. That is harder to maintain.
- Maintenance. Adding a column or index is very arduous when there are zillions of similar tables.
- Analytics. When similar data is spread through tables, it is really hard to answer questions such as "Which client has the most products?".
- Security. Granting access permissions on a single set of tables is less error prone than on zillions of tables.
And no doubt, I've missed a few reasons. You can see that it is almost a no-brainer to have a single database with a small number of tables.
There are situations where separate databases might be called for. I cannot think of a good reason to have separate tables for each client in a single database.
The number one reason would be security and isolation. There might be a business or even legal reason for storing data into "physically" separate databases, to further minimize the possibility of one client seeing another client's data (accidentally or through hacking).
Another reason would be if clients had bespoke solutions. That is, there are per-client customizations. I would still be inclined to try to put this into a single database solution, but that might not be possible.
Related to this would be an application that you intend to support both in the cloud and on premises. In that case, separate databases per client would probably simplify the application design.
But, in general, you would store the data in a pretty normalized single database, with one table per entity.
answered Nov 11 at 12:15
Gordon Linoff
746k33285390
I agree we did not provide enough details in our question. I love you reply and I feel/agree that the 1 table-for-all approach is very attractive. However: Is accessing 1 big table really providing better performance than 10K tables? Security? With 1 table u would need it set at the record level, wouldn't that be more challenging? About "dynamic SQL", would U really hardcode the name of that table in all your code? Updating a column name or its type across 10K tables would be a real nightmare, yes I agree. Any advantages to using the 1 table-per-customer approach?
– MMEL
Nov 14 at 7:39
add a comment |
up vote
2
down vote
You haven't quite provided enough information. Under almost all circumstances (see below for exceptions), you want one set of tables for all customers. Here are some reasons:
- Performance. A proliferation of tables means the data is spread through more data pages, so you have lots of partially filled data pages. The database is bigger and processing is slower.
- Coding efficiency. If the tables for a customer all have different names, then all the code is dynamic SQL. That is harder to maintain.
- Maintenance. Adding a column or index is very arduous when there are zillions of similar tables.
- Analytics. When similar data is spread through tables, it is really hard to answer questions such as "Which client has the most products?".
- Security. Granting access permissions on a single set of tables is less error prone than on zillions of tables.
And no doubt, I've missed a few reasons. You can see that it is almost a no-brainer to have a single database with a small number of tables.
There are situations where separate databases might be called for. I cannot think of a good reason to have separate tables for each client in a single database.
The number one reason would be security and isolation. There might be a business or even legal reason for storing data into "physically" separate databases, to further minimize the possibility of one client seeing another client's data (accidentally or through hacking).
Another reason would be if clients had bespoke solutions. That is, there are per-client customizations. I would still be inclined to try to put this into a single database solution, but that might not be possible.
Related to this would be an application that you intend to support both in the cloud and on premises. In that case, separate databases per client would probably simplify the application design.
But, in general, you would store the data in a pretty normalized single database, with one table per entity.
answered Nov 11 at 12:15
Gordon Linoff
746k33285390
I agree we did not provide enough details in our question. I love you reply and I feel/agree that the 1 table-for-all approach is very attractive. However: Is accessing 1 big table really providing better performance than 10K tables? Security? With 1 table u would need it set at the record level, wouldn't that be more challenging? About "dynamic SQL", would U really hardcode the name of that table in all your code? Updating a column name or its type across 10K tables would be a real nightmare, yes I agree. Any advantages to using the 1 table-per-customer approach?
– MMEL
Nov 14 at 7:39
add a comment |
up vote
2
down vote
up vote
2
down vote
You haven't quite provided enough information. Under almost all circumstances (see below for exceptions), you want one set of tables for all customers. Here are some reasons:
- Performance. A proliferation of tables means the data is spread through more data pages, so you have lots of partially filled data pages. The database is bigger and processing is slower.
- Coding efficiency. If the tables for a customer all have different names, then all the code is dynamic SQL. That is harder to maintain.
- Maintenance. Adding a column or index is very arduous when there are zillions of similar tables.
- Analytics. When similar data is spread through tables, it is really hard to answer questions such as "Which client has the most products?".
- Security. Granting access permissions on a single set of tables is less error prone than on zillions of tables.
And no doubt, I've missed a few reasons. You can see that it is almost a no-brainer to have a single database with a small number of tables.
There are situations where separate databases might be called for. I cannot think of a good reason to have separate tables for each client in a single database.
The number one reason would be security and isolation. There might be a business or even legal reason for storing data into "physically" separate databases, to further minimize the possibility of one client seeing another client's data (accidentally or through hacking).
Another reason would be if clients had bespoke solutions. That is, there are per-client customizations. I would still be inclined to try to put this into a single database solution, but that might not be possible.
Related to this would be an application that you intend to support both in the cloud and on premises. In that case, separate databases per client would probably simplify the application design.
But, in general, you would store the data in a pretty normalized single database, with one table per entity.
answered Nov 11 at 12:15
Gordon Linoff
746k33285390
You haven't quite provided enough information. Under almost all circumstances (see below for exceptions), you want one set of tables for all customers. Here are some reasons:
- Performance. A proliferation of tables means the data is spread through more data pages, so you have lots of partially filled data pages. The database is bigger and processing is slower.
- Coding efficiency. If the tables for a customer all have different names, then all the code is dynamic SQL. That is harder to maintain.
- Maintenance. Adding a column or index is very arduous when there are zillions of similar tables.
- Analytics. When similar data is spread through tables, it is really hard to answer questions such as "Which client has the most products?".
- Security. Granting access permissions on a single set of tables is less error prone than on zillions of tables.
And no doubt, I've missed a few reasons. You can see that it is almost a no-brainer to have a single database with a small number of tables.
There are situations where separate databases might be called for. I cannot think of a good reason to have separate tables for each client in a single database.
The number one reason would be security and isolation. There might be a business or even legal reason for storing data into "physically" separate databases, to further minimize the possibility of one client seeing another client's data (accidentally or through hacking).
Another reason would be if clients had bespoke solutions. That is, there are per-client customizations. I would still be inclined to try to put this into a single database solution, but that might not be possible.
Related to this would be an application that you intend to support both in the cloud and on premises. In that case, separate databases per client would probably simplify the application design.
But, in general, you would store the data in a pretty normalized single database, with one table per entity.
answered Nov 11 at 12:15
Gordon Linoff
746k33285390
answered Nov 11 at 12:15
Gordon Linoff
746k33285390
answered Nov 11 at 12:15
Gordon Linoff
746k33285390
answered Nov 11 at 12:15
Gordon Linoff
746k33285390
746k33285390
I agree we did not provide enough details in our question. I love you reply and I feel/agree that the 1 table-for-all approach is very attractive. However: Is accessing 1 big table really providing better performance than 10K tables? Security? With 1 table u would need it set at the record level, wouldn't that be more challenging? About "dynamic SQL", would U really hardcode the name of that table in all your code? Updating a column name or its type across 10K tables would be a real nightmare, yes I agree. Any advantages to using the 1 table-per-customer approach?
– MMEL
Nov 14 at 7:39
add a comment |
I agree we did not provide enough details in our question. I love you reply and I feel/agree that the 1 table-for-all approach is very attractive. However: Is accessing 1 big table really providing better performance than 10K tables? Security? With 1 table u would need it set at the record level, wouldn't that be more challenging? About "dynamic SQL", would U really hardcode the name of that table in all your code? Updating a column name or its type across 10K tables would be a real nightmare, yes I agree. Any advantages to using the 1 table-per-customer approach?
– MMEL
Nov 14 at 7:39
I agree we did not provide enough details in our question. I love you reply and I feel/agree that the 1 table-for-all approach is very attractive. However: Is accessing 1 big table really providing better performance than 10K tables? Security? With 1 table u would need it set at the record level, wouldn't that be more challenging? About "dynamic SQL", would U really hardcode the name of that table in all your code? Updating a column name or its type across 10K tables would be a real nightmare, yes I agree. Any advantages to using the 1 table-per-customer approach?
– MMEL
Nov 14 at 7:39
I agree we did not provide enough details in our question. I love you reply and I feel/agree that the 1 table-for-all approach is very attractive. However: Is accessing 1 big table really providing better performance than 10K tables? Security? With 1 table u would need it set at the record level, wouldn't that be more challenging? About "dynamic SQL", would U really hardcode the name of that table in all your code? Updating a column name or its type across 10K tables would be a real nightmare, yes I agree. Any advantages to using the 1 table-per-customer approach?
– MMEL
Nov 14 at 7:39
add a comment |
up vote
1
down vote
I think having separate tables (or ideally schemas) for each customer is not that bad idea. In addition to benefits you mentioned, this way you can scale your database easily, and you can give customers full control of their data if they want to.
Regarding the drawbacks:
- Managing it is more complicated but not as bad either - you can write
a script to create columns/tables/indexes/etc. You
don't have to do it manually. - It will be a challenge to perform analytics on 10K tables,
although it's not the best idea to mix it with production anyway.
I'd create a separate database (or server) for analytics, running
some overnight job to update reporting tables.
Also, if your table is going to have hundreds of millions rows (10Kx50k?), it's a good idea to split it into smaller pieces, regardless which option you'll choose. If not by customer, then by region or some other bigger group (assuming you are building on premises RDBMS)
answered Nov 14 at 3:33
Mike Twc
56129
1
"if your table is going to have hundreds of millions rows (10Kx50k?), it's a good idea to split it into smaller pieces". This is just simply false and displays a complete lack of understanding of databases and how they scale.
– Gordon Linoff
Nov 14 at 13:21
In my opinion, smaller tables size would NOT improve performance as no hard-disk driver can access 2 different HD space at the exact same time. Performance would be improved if these "split" tables are placed on a completely different server. Again, if the file is too big, it means too many customers using the same file, it means too many requests at the same time, so yes to splitting but then move that other file to a different server.
– MMEL
Nov 14 at 18:59
add a comment |
up vote
1
down vote
I think having separate tables (or ideally schemas) for each customer is not that bad idea. In addition to benefits you mentioned, this way you can scale your database easily, and you can give customers full control of their data if they want to.
Regarding the drawbacks:
- Managing it is more complicated but not as bad either - you can write
a script to create columns/tables/indexes/etc. You
don't have to do it manually. - It will be a challenge to perform analytics on 10K tables,
although it's not the best idea to mix it with production anyway.
I'd create a separate database (or server) for analytics, running
some overnight job to update reporting tables.
Also, if your table is going to have hundreds of millions rows (10Kx50k?), it's a good idea to split it into smaller pieces, regardless which option you'll choose. If not by customer, then by region or some other bigger group (assuming you are building on premises RDBMS)
answered Nov 14 at 3:33
Mike Twc
56129
1
"if your table is going to have hundreds of millions rows (10Kx50k?), it's a good idea to split it into smaller pieces". This is just simply false and displays a complete lack of understanding of databases and how they scale.
– Gordon Linoff
Nov 14 at 13:21
In my opinion, smaller tables size would NOT improve performance as no hard-disk driver can access 2 different HD space at the exact same time. Performance would be improved if these "split" tables are placed on a completely different server. Again, if the file is too big, it means too many customers using the same file, it means too many requests at the same time, so yes to splitting but then move that other file to a different server.
– MMEL
Nov 14 at 18:59
add a comment |
up vote
1
down vote
up vote
1
down vote
I think having separate tables (or ideally schemas) for each customer is not that bad idea. In addition to benefits you mentioned, this way you can scale your database easily, and you can give customers full control of their data if they want to.
Regarding the drawbacks:
- Managing it is more complicated but not as bad either - you can write
a script to create columns/tables/indexes/etc. You
don't have to do it manually. - It will be a challenge to perform analytics on 10K tables,
although it's not the best idea to mix it with production anyway.
I'd create a separate database (or server) for analytics, running
some overnight job to update reporting tables.
Also, if your table is going to have hundreds of millions rows (10Kx50k?), it's a good idea to split it into smaller pieces, regardless which option you'll choose. If not by customer, then by region or some other bigger group (assuming you are building on premises RDBMS)
answered Nov 14 at 3:33
Mike Twc
56129
I think having separate tables (or ideally schemas) for each customer is not that bad idea. In addition to benefits you mentioned, this way you can scale your database easily, and you can give customers full control of their data if they want to.
Regarding the drawbacks:
- Managing it is more complicated but not as bad either - you can write
a script to create columns/tables/indexes/etc. You
don't have to do it manually. - It will be a challenge to perform analytics on 10K tables,
although it's not the best idea to mix it with production anyway.
I'd create a separate database (or server) for analytics, running
some overnight job to update reporting tables.
Also, if your table is going to have hundreds of millions rows (10Kx50k?), it's a good idea to split it into smaller pieces, regardless which option you'll choose. If not by customer, then by region or some other bigger group (assuming you are building on premises RDBMS)
answered Nov 14 at 3:33
Mike Twc
56129
answered Nov 14 at 3:33
Mike Twc
56129
answered Nov 14 at 3:33
Mike Twc
56129
answered Nov 14 at 3:33
Mike Twc
56129
56129
1
"if your table is going to have hundreds of millions rows (10Kx50k?), it's a good idea to split it into smaller pieces". This is just simply false and displays a complete lack of understanding of databases and how they scale.
– Gordon Linoff
Nov 14 at 13:21
In my opinion, smaller tables size would NOT improve performance as no hard-disk driver can access 2 different HD space at the exact same time. Performance would be improved if these "split" tables are placed on a completely different server. Again, if the file is too big, it means too many customers using the same file, it means too many requests at the same time, so yes to splitting but then move that other file to a different server.
– MMEL
Nov 14 at 18:59
add a comment |
1
"if your table is going to have hundreds of millions rows (10Kx50k?), it's a good idea to split it into smaller pieces". This is just simply false and displays a complete lack of understanding of databases and how they scale.
– Gordon Linoff
Nov 14 at 13:21
In my opinion, smaller tables size would NOT improve performance as no hard-disk driver can access 2 different HD space at the exact same time. Performance would be improved if these "split" tables are placed on a completely different server. Again, if the file is too big, it means too many customers using the same file, it means too many requests at the same time, so yes to splitting but then move that other file to a different server.
– MMEL
Nov 14 at 18:59
1
1
"if your table is going to have hundreds of millions rows (10Kx50k?), it's a good idea to split it into smaller pieces". This is just simply false and displays a complete lack of understanding of databases and how they scale.
– Gordon Linoff
Nov 14 at 13:21
"if your table is going to have hundreds of millions rows (10Kx50k?), it's a good idea to split it into smaller pieces". This is just simply false and displays a complete lack of understanding of databases and how they scale.
– Gordon Linoff
Nov 14 at 13:21
In my opinion, smaller tables size would NOT improve performance as no hard-disk driver can access 2 different HD space at the exact same time. Performance would be improved if these "split" tables are placed on a completely different server. Again, if the file is too big, it means too many customers using the same file, it means too many requests at the same time, so yes to splitting but then move that other file to a different server.
– MMEL
Nov 14 at 18:59
In my opinion, smaller tables size would NOT improve performance as no hard-disk driver can access 2 different HD space at the exact same time. Performance would be improved if these "split" tables are placed on a completely different server. Again, if the file is too big, it means too many customers using the same file, it means too many requests at the same time, so yes to splitting but then move that other file to a different server.
– MMEL
Nov 14 at 18:59
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53246054%2fdatabase-architecture-question-1-table-per-customer-or-1-unique-table-for-all-c%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
5
Option #3 is the only one which makes sense and is remotely scalable. Don't use the first two options.
– Tim Biegeleisen
Nov 11 at 5:22
2
There is no good or bad answer. It all depends on your requirements. What is the number of customers, what is their lifecycle (will they be added or not), will you have features same for all customers always, how will you manage bug fixes for each customer etc. etc. While @TimBiegeleisen, is pointed for option #3 as default, if it is multi-tenant setup for example, where customers should have access to their data only, you may choose option #2 as well. This will make management of your customers independent from each other for example.
– muradm
Nov 11 at 5:28
Thank you both for your comments. We would have 10 thousand customers who might all have all 50 thousand products each. 1 huge SQL table as in solution 3 is attractive, however always accessing the same table for any customer may bring some performance issues, also if for whatever reason that table gets corrupted then all customers lose their access. Also, If that unique SQL table gets hacked, the hacker has access to all customers data with that 1 table. Even then I am still not sure which solution is best?
– MMEL
Nov 11 at 5:37
Hi. This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on Minimal, Complete, and Verifiable example. PS Please clarify via post edits, not commments.
– philipxy
Nov 11 at 7:58