Имеется 4 таблицы, в каждой из которых хранится вид транспорта (автомобиль, метро, автобус, катер) каждая из таблиц имеет первичный ключ. Есть 5-я таблица, которая содержит данные о разных параметрах транспорта, помимо того, какой из 4-х был использован. Подумал, что можно сделать таблицу со следующими полями: user_id, auto_id, metro_id, bus_id, boat_id, но проблема заключается в том, что человек мог использовать не все виды транспорта за 1 день, соответственно половина полей могут быть пусты. Еще 1 вариант сделать 5-ю таблицу в виде: user_id, transport_id. Но в данном случае могут быть повторения первичных ключей (то есть транспорт разный, а transport_id одинаковый будет).
Как правильно реализовать данную структуру данных?
Ihor Kalashnikov Выходит, что внешних ключей таблица не будет иметь или только на таблицу с типами? То есть таблицы с транспортом и объединяющая таблица "сами по себе" без связей?
Почему без связи, просто в таблице user2transport ключ составной, одна часть это тип транспорта, другая часть это его id. Тут конечно нужно где-то описать и сохранить логику ключа, например в хранимую процедуру, или можно какой-нибудь комментарий написать, или вынести ключ в отдельную таблицу, transport_type2table. Чтоб потом было ясно как этим пользоваться. А можно ничего не делать, зависит от того, что вы там делаете и для кого.
Ihor Kalashnikov: Кажется, я не разобрался :) У меня выходит так, что у одного потомка есть 4 предка в "общей" таблице: user_id, auto_id, metro_id, bus_id, boat_id. Так работать не будет. Тут получается, что при смене ID родителя мы попытаемся сменить ID других родителей - бред какой-то.
Ihor Kalashnikov: Если вы это имели в виду: i.imgur.com/OG1vUJs.png то да, ясно было изначально. Только то, что здесь нарисовано - чушь или же до меня не доходит.
почему же чушь? вы хотите много таблиц для разного транспорта, но для таблицу для связи транспорта и юзера вы хотите одну, значит указателем на транспорт является не только id (хотя есть варианты, если например id уникален не в рамках таблицы а базы), указателем на транспорт является пара ("имя таблицы", "id транспорта в этой таблице"), собственно поэтому я и сказал, что можно назвать колонку transport_table(вместо _type), но тогда если будут изменения в названии таблиц и вообще с точки зрения здравого смысла, лучше использовать для этого ENUM, на картинке выше по сути я его и нарисовал.
Кстати таблица вида user_id, boat_id, bus_id, ... плоха еще тем, что когда вы будете добавлять новый транспорт, придется альтертейблы писать каждый раз.
Если вы решаете задачку какую-то учебную по базам данных, то мета информацию придется хранить как на картинке. Если же это боевой проект, то эту логику маппинга типа транспорта из колонки на таблицу, часто хранят на уровне приложения, когда пишут запросы, или же пишут вьюшку, которая удобно все выгребает, из нужных таблиц. Отдельную таблицу вряд ли бы делали, только если бы таблиц с транспортом было много.
Вы не понимаете, как написать запрос, который в зависимости от значения tranport_type выведет значения из определенной таблицы?
Ihor Kalashnikov: Понимаю, просто хотел сохранить целостность БД, чтобы в случае какой-то ошибки или смене ID у родителя, менялся там, где должен. Смотрел массу примеров, в которых почти все в БД таблицы связаны, при этом открыл БД движков ДЛЕ и ВОРДПРЕСС, там вообще нет внешних ключей, не знаю почему.
Что касается вашего первого абзаца: ту модель, что вы нарисовали, я ее перерисовал более наглядно (думаю, что правильно), там как раз проблема в том, что при смене ID родителя должен меняться определенный ID в таблице "user_transport", но он просто не может меняться, потому что он не захватывает тот самый тип, если же писать запрос, то можно учесть, что с такими то ID типами поменять значения... Но это не на уровне БД получается, что не есть хорошо, отсюда вытекает то, что связи не нужны. Вообще, если перенести эту диаграмму в рабочую БД, то последняя видит только 4 связь из 4-х (то есть один из 4-х внешних ключей), что является верным решением, скажем так.
Что касается добавления новых типов (таблиц) - их просто нет больше, так что это не проблема. (В рамках моего проекта, скажем так)
Пу сути, это звучит глупо, но я пытаюсь сделать внешний ключ, который будет являться составным и учитывать в себе и тип (который находится вообще в другой таблице) и идентификатор типа, я не понимаю, как это сделать. Сделать так, чтобы внешний ключ ссылался на 2 таблицы сразу нельзя, уже прочитал и я в тупике.
Просто хочу делать хорошо, а не так, чтобы просто работало.
Понял, что у меня несколько вариантов:
1. Сделать так, как вы посоветовали изначально, избавиться от ключей и передать контроль за целостностью приложению.
2. Подумать еще :)
3. Попробовать объединить все типы в одну таблицу, тогда будут и внешние ключи и целостность будет на БД. Но будет много пустых полей или null... Плохо ли это скажется на качестве БД? Имею в виду скорость и вообще правильность.
С эти разобрались, что надо добавить тип. Непонятно как связать таблицы. Потому что есть 4 типа транспорта - это 4 таблицы, в "главной" таблице содержится 3 поля: имя, тип и ид транспорта, если добавлять внешние ключи, то родителями будут выступать таблицы с описанием транспорта и все связи будут идти от поля ид транспорта - что работать не будет, потому что я не могу изменить через родителя значение другого родителя, да и мне это не требуется. Нужно как-то сделать чтобы учитывался тип, при изменении ID в параметрах транспорта.
sim3x: Нет. Пока она вообще не нужна. Пытаюсь разделить по категориям, чтобы было просто и удобно ориентироваться. Я сомневаюсь, что я на пути к денормализации. Взять в пример любой ИМ, все товары хранятся в одной таблице? В любом случае все делится на категории, у которых разные параметры. Взять юлмарт, который продает какие-нибудь материнские платы и гречку, вряд ли это хранится в одной таблице.
Ваш способ интересный. Только вот, тут не хватает присваивания идентификатора внутри самого типа. То есть параметры и их значения еще делятся на подгруппы, как-то так. То есть в каждом параметре огромное количество разных параметров + их значений, а пользователю нужно предоставить именно его параметр со значением.
Средний
Средний
