mysql - 许多表到一个表 - 多个条目

I have a system which has (for the sake of a simple example) tables consisting of Customers, Vendors, Products, Sales, etc. Total 24 tables at present.

我有一个系统(由于一个简单的例子),表包括客户,供应商,产品,销售等。目前共有24个表。

I want to add the ability to have multiple notes for each record in these tables. E.g., Each Customers record could have 0 to many notes, each Vendor record could have 0 to many notes, etc.

我想为这些表中的每条记录添加多个注释的功能。例如,每个客户记录可以有0到多个票据,每个供应商记录可以有0到多个票据等。

My thinking is that I would like to have one "Notes" table, indexed by a Noteid and Date/time stamp. The actual note data would be a varchar(255).

我的想法是,我希望有一个“Notes”表,由Noteid和日期/时间戳索引。实际的音符数据是varchar(255)。

I am looking for a creative way to bi-directionally tie the source tables to the Notes table. The idea of having 24 foreign key type cross reference tables or 24 Notes tables doesn't really grab me.

我正在寻找一种创造性的方法来将源表双向绑定到Notes表。拥有24个外键类型交叉引用表或24个Notes表的想法并没有真正抓住我。

Programming is being done in PHP with Apache server. Database is mysql/InnoDB.

使用Apache服务器在PHP中进行编程。数据库是mysql / InnoDB。

Open to creative ideas.

开放创意。

Thanks

Ralph

4 个解决方案

#1

I would sugges a table like this

我会建议这样一张桌子

note_id  :  int autoincrement primary
type_id  :  int, foreign key from f Customers, Vendors, Products etc 
type     :  varchar, code indicating the type, like Vendors, VENDORS or just V 
note     :  varchar, the actual node


CREATE TABLE IF NOT EXISTS `notes` (
  `note_id` int(11) NOT NULL AUTO_INCREMENT,
  `type_id` int(11) NOT NULL,
  `type` varchar(20) CHARACTER SET utf8 NOT NULL,
  `note` varchar(255) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`note_id`)
) 

With a setup like that you can have multiple notes for each type, like Vendors, and also hold notes for multiple types.

通过这样的设置,您可以为每种类型提供多个注释,例如供应商,还可以保存多种类型的注释。

data sample

note_id     type_id     type          note
--------------------------------------------------------------------
1           45          Vendors       a note 
2           45          Vendors       another note
3           3           Customers     a note for customer #3
4           67          Products      a note for product #67

SQL sample

select note from notes where type="Vendors" and type_id=45

To reduce table size, I would prefer aliases for the types, like V, P, C and so on.

为了减少表格大小,我更喜欢类型的别名,如V,P,C等。

#2

Don't do a "universal" table, e.g.

不要做“通用”表格,例如

id, source_table, source_record_id, note_text

id,source_table,source_record_id,note_text

might sound good in practice, but you can NOT join this table against your others without writing dynamic SQL.

在实践中可能听起来不错,但是如果不编写动态SQL,就不能将这个表与其他人联系起来。

It's far better to simply add a dedicated notes field to every table. This eliminates any need for dynamic sql, and the extra space usage will be minimal if you use varchar/text fields, since those aren't stored in-table anyways.

简单地为每个表添加一个专用的注释字段要好得多。这消除了对动态sql的任何需求,并且如果使用varchar / text字段,额外的空间使用将是最小的,因为那些不是存储在表中的。

#3

I've done a structure like this before where I used a format like this:

在我使用这样的格式之前,我已经完成了这样的结构:

id (int)
target_type (enum/varchar)
target_id (int)
note (text)

Each data element just has to query for it's own type then, so for your customer object you would query for notes attached to it like this

每个数据元素只需查询它自己的类型,因此对于您的客户对象,您将查询附加到它的注释,如下所示

SELECT * FROM notes where target_type='customer' AND target_id=$this->id

You can also link target_type to the actual class, so that you write to the database using get_class($this) to fill out target type, in which case a single function inside of the Note class could take in any other object type you have.

您还可以将target_type链接到实际的类,以便使用get_class($ this)写入数据库以填充目标类型,在这种情况下,Note类中的单个函数可以接受您拥有的任何其他对象类型。

#4

In my opinion, there isn't a clean solution for this.

在我看来,没有一个干净的解决方案。

option 1: Master entity table

Every (relevant) row of every (relevant) table has a master entry inside a table (let's call it entities_tbl. The ids of each derived table isn't an autoincrement but it's a foreign key referencing the master table.

每个(相关)表的每个(相关)行在表中都有一个主条目(我们称之为entities_tbl。每个派生表的ID不是自动增量,而是引用主表的外键。

Now you can easily link the notes table with the master entity id.

现在,您可以轻松地将notes表与主实体ID链接起来。

PRO: It's an object oriented idea. Like a base "Object" class which is the father of every other class. Also, each entity has an unique id across the database.

PRO:这是一个面向对象的想法。就像一个基础“对象”类,它是每个其他类的父亲。此外,每个实体在数据库中都有唯一的ID。

CON: It's a mess. Every entity ID is scattered among (at least) two tables. You'd need JOINs every single time, and the master entity table will be HUGE (it will contain the same number of rows as the sum of every other child table, combined)

CON:这是一团糟。每个实体ID分散在(至少)两个表中。您每次都需要JOIN,并且主实体表将是巨大的(它将包含与每个其他子表的总和相同的行数,组合)

option 2: meta-attributes

inside the notes table, the primary key would contain an autoincrement, the entity_id and item_table_name. This way you can easily extract the notes of any entity from any table.

在notes表中,主键包含一个自动增量,即entity_id和item_table_name。这样,您可以轻松地从任何表中提取任何实体的注释。

PRO: Easy to write, easy to populate CON: It needs meta-values to extract real values. No foreign keys to grant referential integrity, messy and sloppy joins, table names as where conditions.

PRO:易于编写,易于填充CON:它需要元值来提取实际值。没有外键可以授予引用完整性,凌乱和草率的连接,表名作为条件。

option 3: database denormalization

(sigh, I've never considered to ever give this suggestion) Add a column inside each table where you need notes. Store the notes as json encoded strings. (this means to denormalize a database because you will introduce non-atomic values)

(叹气,我从来没有考虑过给出这个建议)在每个需要注释的表格中添加一列。将注释存储为json编码的字符串。 (这意味着对数据库进行非规范化,因为您将引入非原子值)

PRO: easy and fast to write, uses some form of standard even for future database users, the notes are centralized and easily accessible from each entity CON: the database isn't normalized, poor search and comparison between notes

PRO:编写简单快捷,即使对于未来的数据库用户也使用某种形式的标准,这些注释是集中的,可以从每个实体轻松访问CON:数据库没有规范化,差异搜索和注释之间的比较