How to design database and organize data if I have two or more domains and one database? All sites for e-commerce purpose and one good can be for sale on each site. So I have only two ideas:
如果我有两个或多个域和一个数据库,如何设计数据库和组织数据?所有用于电子商务目的的网站和一个商品都可以在每个网站上出售。所以我只有两个想法:
- I need to create one more field (site_id) in almost every table and duplicate data.
- I need to create one table with site_id information for all other fields from other tables.
我需要在几乎每个表中再创建一个字段(site_id)并复制数据。
我需要为其他表中的所有其他字段创建一个包含site_id信息的表。
Both of ideas have a huge minuses. Any ideas? Thanks.
这两种想法都有很大的缺陷。有任何想法吗?谢谢。
5 个解决方案
#1
It is likely that there are a small handful of tables somewhere in your schema that link to all of your other tables. It is these tables that you need to put the site_id's in, not every table in your database.
您的架构中的某些地方可能会有少量几个表链接到您的所有其他表。您需要将这些表放入site_id,而不是数据库中的每个表。
For (a highly contrived) example, if my schema includes a Customers table, an Invoices table, and an Invoice Line Items table, I don't need site_id's in all three tables. I only need a site_id in the Customer's table.
对于(一个非常人为的)示例,如果我的架构包括Customers表,Invoices表和Invoice Line Items表,我在所有三个表中都不需要site_id。我只需要在Customer表中使用site_id。
#2
This is a classic problem when building multi-tenant systems. I've heard a few different opinions on this matter, but they basically break down into two camps:
这是构建多租户系统时的典型问题。我在这个问题上听到了一些不同的意见,但它们基本上分为两个阵营:
-
Use the tenant id (
site_idin your case) on every table which contains data for a specific tenant. Advocates of this approach cite ease of identifying the tenant that data belongs to as a primary benefit with implications for how the data is archived (viz. different tablespaces for different customers).在包含特定租户数据的每个表上使用租户ID(在您的情况下为site_id)。这种方法的倡导者认为,易于识别数据所属的租户是主要的好处,对数据的存档方式有影响(即不同客户的不同表空间)。
-
Use the tenant id only on high-level tables. Advocates of this approach typically describe the benefits being a cleaner database structure.
仅在高级表上使用租户ID。这种方法的倡导者通常将这些好处描述为更清洁的数据库结构。
I'n not a fan of creating different physical tables for the same type of data from different customers. There are a number of unfavorable consequences to this:
我不喜欢为来自不同客户的相同类型的数据创建不同的物理表。这会产生一些不利后果:
- It becomes difficult to create a coherent object model via an ORM tool
- This approach doesn't scale well with large number of customers -- If you have 70,000 customers that must be serviced from a single database, you'll have 70,000 sets of tables.
- Table names must be generated dynamically for SQL statements.
通过ORM工具创建连贯的对象模型变得很困难
这种方法无法很好地适应大量客户 - 如果您有70,000个客户必须从单个数据库进行维护,那么您将拥有70,000套表。
必须为SQL语句动态生成表名。
#3
I think one of the approaches used by Wordpress and Drupal is to prefix tables with a name:
我认为Wordpress和Drupal使用的方法之一是使用名称为表添加前缀:
dom1_Customers
dom2_Customers
This way the tables don't grow out of proportion and you don't have to maintain an extra index of site_id. That said, your code has to compensate for it, which can require some reinstrumentation (and stored procedures are basically out without some nastiness).
这样,表格不会成比例增长,您不必维护site_id的额外索引。也就是说,您的代码必须对其进行补偿,这可能需要一些重新检测(并且存储过程基本上没有一些肮脏)。
#4
My preference is to create mapping tables where needed. Think that a Product can exist for Site 1, Site 2, etc. The product details don't change across the sites. The products price might though! In which case the Prices table might need the SiteID and the ProductID where ProductID may be replicated for each entry across the different sites. This could be said for users too except that users may find this to be "big brother" in feeling. So while this might work for customers I generally suggest that customers have different accounts across different sites! Sometimes what will physically work doesn't mean that it logically will work. Put the SiteID where you will need it rather than just haphazardly putting it every where. Keep in mind that you may need this SiteID in places beyond where you application needs it...think about offline querying too. Having to do 5 joins to filter by the SiteID will suck! Maintaining the indexes is better than having to hunt for the filter!
我的偏好是在需要的地方创建映射表。认为产品可以存在于站点1,站点2等。产品详细信息不会在站点之间发生变化。虽然产品价格可能!在这种情况下,Price表可能需要SiteID和ProductID,其中可以为不同站点的每个条目复制ProductID。除了用户可能会觉得这是“大哥哥”之外,用户也可以这样说。因此,尽管这可能对客户有效,但我通常建议客户在不同的网站上拥有不同的帐户!有时候,身体上的工作并不意味着它在逻辑上会起作用。将SiteID放在您需要它的位置,而不是随便将它放在任何地方。请记住,您可能需要在应用程序需要之外的地方使用此SiteID ...请考虑离线查询。必须通过SiteID进行5次连接才能过滤掉!维护索引比不得不寻找过滤器更好!
With regards to horizontal partitioning by way of separate tables with similar names...use SQL Server 2005 and up. It has features for partitioning so that worrying about data size is no longer an issue.
关于通过具有相似名称的单独表进行水平分区...使用SQL Server 2005及更高版本。它具有分区功能,因此担心数据大小不再是问题。
#5
Is multi-database out of the question? It seems to be the easiest and the cleanest one, you cannot possibly mess one tennants data with another one's.. You are gonna need one Master database for tenant info, and one db for each tennant.
多数据库是不可能的?它似乎是最简单和最干净的一个,你不可能将一个Tennants数据与另一个数据混乱。你需要一个Master数据库用于租户信息,每个Tennant需要一个db。
#1
It is likely that there are a small handful of tables somewhere in your schema that link to all of your other tables. It is these tables that you need to put the site_id's in, not every table in your database.
您的架构中的某些地方可能会有少量几个表链接到您的所有其他表。您需要将这些表放入site_id,而不是数据库中的每个表。
For (a highly contrived) example, if my schema includes a Customers table, an Invoices table, and an Invoice Line Items table, I don't need site_id's in all three tables. I only need a site_id in the Customer's table.
对于(一个非常人为的)示例,如果我的架构包括Customers表,Invoices表和Invoice Line Items表,我在所有三个表中都不需要site_id。我只需要在Customer表中使用site_id。
#2
This is a classic problem when building multi-tenant systems. I've heard a few different opinions on this matter, but they basically break down into two camps:
这是构建多租户系统时的典型问题。我在这个问题上听到了一些不同的意见,但它们基本上分为两个阵营:
-
Use the tenant id (
site_idin your case) on every table which contains data for a specific tenant. Advocates of this approach cite ease of identifying the tenant that data belongs to as a primary benefit with implications for how the data is archived (viz. different tablespaces for different customers).在包含特定租户数据的每个表上使用租户ID(在您的情况下为site_id)。这种方法的倡导者认为,易于识别数据所属的租户是主要的好处,对数据的存档方式有影响(即不同客户的不同表空间)。
-
Use the tenant id only on high-level tables. Advocates of this approach typically describe the benefits being a cleaner database structure.
仅在高级表上使用租户ID。这种方法的倡导者通常将这些好处描述为更清洁的数据库结构。
I'n not a fan of creating different physical tables for the same type of data from different customers. There are a number of unfavorable consequences to this:
我不喜欢为来自不同客户的相同类型的数据创建不同的物理表。这会产生一些不利后果:
- It becomes difficult to create a coherent object model via an ORM tool
- This approach doesn't scale well with large number of customers -- If you have 70,000 customers that must be serviced from a single database, you'll have 70,000 sets of tables.
- Table names must be generated dynamically for SQL statements.
通过ORM工具创建连贯的对象模型变得很困难
这种方法无法很好地适应大量客户 - 如果您有70,000个客户必须从单个数据库进行维护,那么您将拥有70,000套表。
必须为SQL语句动态生成表名。
#3
I think one of the approaches used by Wordpress and Drupal is to prefix tables with a name:
我认为Wordpress和Drupal使用的方法之一是使用名称为表添加前缀:
dom1_Customers
dom2_Customers
This way the tables don't grow out of proportion and you don't have to maintain an extra index of site_id. That said, your code has to compensate for it, which can require some reinstrumentation (and stored procedures are basically out without some nastiness).
这样,表格不会成比例增长,您不必维护site_id的额外索引。也就是说,您的代码必须对其进行补偿,这可能需要一些重新检测(并且存储过程基本上没有一些肮脏)。
#4
My preference is to create mapping tables where needed. Think that a Product can exist for Site 1, Site 2, etc. The product details don't change across the sites. The products price might though! In which case the Prices table might need the SiteID and the ProductID where ProductID may be replicated for each entry across the different sites. This could be said for users too except that users may find this to be "big brother" in feeling. So while this might work for customers I generally suggest that customers have different accounts across different sites! Sometimes what will physically work doesn't mean that it logically will work. Put the SiteID where you will need it rather than just haphazardly putting it every where. Keep in mind that you may need this SiteID in places beyond where you application needs it...think about offline querying too. Having to do 5 joins to filter by the SiteID will suck! Maintaining the indexes is better than having to hunt for the filter!
我的偏好是在需要的地方创建映射表。认为产品可以存在于站点1,站点2等。产品详细信息不会在站点之间发生变化。虽然产品价格可能!在这种情况下,Price表可能需要SiteID和ProductID,其中可以为不同站点的每个条目复制ProductID。除了用户可能会觉得这是“大哥哥”之外,用户也可以这样说。因此,尽管这可能对客户有效,但我通常建议客户在不同的网站上拥有不同的帐户!有时候,身体上的工作并不意味着它在逻辑上会起作用。将SiteID放在您需要它的位置,而不是随便将它放在任何地方。请记住,您可能需要在应用程序需要之外的地方使用此SiteID ...请考虑离线查询。必须通过SiteID进行5次连接才能过滤掉!维护索引比不得不寻找过滤器更好!
With regards to horizontal partitioning by way of separate tables with similar names...use SQL Server 2005 and up. It has features for partitioning so that worrying about data size is no longer an issue.
关于通过具有相似名称的单独表进行水平分区...使用SQL Server 2005及更高版本。它具有分区功能,因此担心数据大小不再是问题。
#5
Is multi-database out of the question? It seems to be the easiest and the cleanest one, you cannot possibly mess one tennants data with another one's.. You are gonna need one Master database for tenant info, and one db for each tennant.
多数据库是不可能的?它似乎是最简单和最干净的一个,你不可能将一个Tennants数据与另一个数据混乱。你需要一个Master数据库用于租户信息,每个Tennant需要一个db。