|
Структура таблиц
#39265186
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
Доброго времени суток форумчане.
Такая ситуация, есть следующие таблицы:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
mysql> SHOW CREATE TABLE history;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| history | CREATE TABLE `history` (
`no` int(11) NOT NULL AUTO_INCREMENT,
`crt_date` datetime NOT NULL,
`crt_user` int(3) DEFAULT NULL,
`id` int(11) DEFAULT NULL,
`txt` mediumtext,
PRIMARY KEY (`no`),
UNIQUE KEY `combine` (`no`,`crt_date`),
KEY `crt_date` (`crt_date`),
KEY `id` (`id`),
KEY `crt_user` (`crt_user`)
) ENGINE=MyISAM AUTO_INCREMENT=941629 DEFAULT CHARSET=utf8 |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
mysql> SHOW CREATE TABLE fileattach;
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| fileattach | CREATE TABLE `fileattach` (
`no` int(11) NOT NULL AUTO_INCREMENT,
`case_no` int(11) DEFAULT NULL,
`creator` int(3) NOT NULL,
`date` datetime DEFAULT NULL,
`filename` tinytext NOT NULL,
`filesize` int(11) DEFAULT NULL,
`path` tinytext NOT NULL,
`gop_no` int(11) DEFAULT NULL,
PRIMARY KEY (`no`),
KEY `creator` (`creator`),
KEY `case_no` (`case_no`)
) ENGINE=MyISAM AUTO_INCREMENT=285522 DEFAULT CHARSET=utf8 |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
mysql> SHOW CREATE TABLE tasklist;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tasklist | CREATE TABLE `tasklist` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`no` int(11) NOT NULL,
`claim_id` decimal(20,2) DEFAULT NULL,
`crt_date` datetime NOT NULL,
`crt_ou` int(1) NOT NULL,
`deadline` datetime NOT NULL,
`complete_time` datetime DEFAULT NULL,
`task` text NOT NULL,
`comments_for_task` varchar(300) DEFAULT NULL,
`creator` int(3) NOT NULL,
`task_for` int(3) NOT NULL DEFAULT '1',
`performer` int(3) DEFAULT '1',
`complited` int(1) NOT NULL DEFAULT '2',
`view_count` int(3) NOT NULL DEFAULT '0',
`root` int(1) NOT NULL DEFAULT '1',
`parent_id` int(11) DEFAULT NULL,
`active_task` int(1) NOT NULL DEFAULT '0',
`auto_task_id` int(2) DEFAULT NULL,
`for_stat` int(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
UNIQUE KEY `perform` (`performer`,`deadline`,`id`),
KEY `no` (`no`),
KEY `complited` (`complited`),
KEY `crt_date` (`crt_date`),
KEY `creator` (`creator`),
KEY `performer` (`performer`),
KEY `parent_id` (`parent_id`),
KEY `deadline` (`deadline`),
KEY `combine` (`creator`,`performer`,`no`),
KEY `crt_ou` (`crt_ou`),
KEY `test` (`performer`,`auto_task_id`)
) ENGINE=MyISAM AUTO_INCREMENT=500521 DEFAULT CHARSET=utf8 |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
mysql> SHOW CREATE TABLE assist_mail;
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| assist_mail | CREATE TABLE `assist_mail` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`case_id` int(11) NOT NULL,
`creator` int(3) NOT NULL,
`sender` text NOT NULL,
`recipient` text NOT NULL,
`date_send` datetime DEFAULT NULL,
`date_added` datetime NOT NULL,
`date_rec` datetime DEFAULT NULL,
`subject` text NOT NULL,
`mail_text` longtext NOT NULL,
`tb` int(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `combine1` (`creator`,`case_id`,`date_added`,`tb`),
UNIQUE KEY `combine2` (`creator`,`case_id`,`date_send`,`tb`),
KEY `case_id` (`case_id`),
KEY `creator` (`creator`),
KEY `date_send` (`date_send`),
KEY `date_added` (`date_added`),
KEY `tb` (`tb`)
) ENGINE=MyISAM AUTO_INCREMENT=541952 DEFAULT CHARSET=utf8 |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Одинаковые сущности только creator/crt_user,crt_date,case_no/case_id т.е. очень мало.
Стоит задача выводить информацию из каждой таблицы в едином списке сгруппированном по дате этого события, создать некий timeline по событию, в котором будет отображаться все и записи в истории и прикрепленные файлы и созданные задачи и полученные/отправленные письма.
Ломаю голову как это можно организовать. Создавать новую таблицу в которой будут все эти поля и новое поле например type=1,2,3,4 (history,file,task,mail) или сделать сводную таблицу с указанием ID события в основной таблице и типом
или оставлять таблицы как есть а работать дальше уже с результатами выборок из каждой?
Какой вариант лучше или может быть есть еще какой-то? |
|
|
