Доброго времени суток, коллеги!
Хочу проконсультроваться по организации БД для следующего случая: см. схему.
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.
таблица: Заказы
+--------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+-------+
| order_id | int( 10 ) unsigned | | PRI | 0 | |
| user_id | int( 10 ) unsigned | | MUL | 0 | |
| payment_date | datetime | YES | | NULL | |
| perform_date | datetime | YES | | NULL | |
+--------------+------------------+------+-----+---------+-------+
таблица: файлы с ключами к продуктам
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| key_id | int( 10 ) unsigned | | PRI | NULL | auto_increment |
| product_id | int( 10 ) unsigned | YES | MUL | NULL | |
| order_id | int( 10 ) unsigned | YES | MUL | NULL | |
| filename | varchar( 50 ) | | | | |
+------------+------------------+------+-----+---------+----------------+
таблица: продукты
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| product_id | int( 10 ) unsigned | | PRI | NULL | auto_increment |
| title | varchar( 50 ) | | | | |
| price | float | | | 0 | |
+------------+------------------+------+-----+---------+----------------+
ключи для таблицы: ключи к продуктам
+-------------+------------+------------+--------------+-------------+-----------+-------------+------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Null |
+-------------+------------+------------+--------------+-------------+-----------+-------------+------+
| product_key | 0 | PRIMARY | 1 | key_id | A | 0 | |
| product_key | 1 | product_id | 1 | product_id | A | 0 | YES |
| product_key | 1 | order_id | 1 | order_id | A | 0 | YES |
+-------------+------------+------------+--------------+-------------+-----------+-------------+------+
Итак...
С каждым продуктом связаны уникальные ключи его активации.
Физически они представляют собой файлы на сервере (поле filename в таблице product_key).
Эти ключи покупают юзеры (при этом, ключ ассоциируется с заказом юзера: order_id).
Необходимо грамотно разрулить ситуации:
1. Удаление продукта (файлы, связанные с продуктом должны быть удалены, но файлы, связанные с заказом удаляться не должны)
2. Удаление заказа (файлы, связанные с заказом должны быть удалены)
Возможные проблемы:
Если удалили заказ, то поле order_id будет NULL у product_key.
Но этот файл повторно продавать нельзя!
Вижу следующий выход:
При удалении продукта ставим product_id=NULL.
При ассоциировании файла с заказом ставим файлу соотв. order_id +еще один флаг (used=1).
При удалении заказа ставим у файла order_id=NULL.
Для чистки таблицы product_key по расписанию выполняем запрос:
DELETE FROM product_key WHERE order_id=NULL AND (product_id=NULL OR used= 1 )
т.е. удаляем все записи для несуществующих продуктов и для удаленных заказов (где used=1, а order_id=NULL)
Надеюсь, изложил задачу достаточно понятно...
Собственно, вопросы:
Есть ли более элегантные варианты решения поставленной задачи?
Можно ли в данном случае обойтись только средствами SQL? (т.е. ограничениями FK и проч.)
Спасибо.
PS:
MySQL 4.1
