Помогите разработать БД. Имеем VIN (Идентификационный номер транспортного средства) и (вариант, версию или номер одобрения типа) нужно по нему узнать характеристики авто, производителя, марку.
Примерная схема прикреплена. Что-то можно упростить или нужно добавить?

еще вопросик
Идентификационный номер транспортного средства (англ. Vehicle identification number, VIN) — уникальный код транспортного средства, состоящий из 17 символов.
В VIN разрешено использовать только следующие символы латинского алфавита и арабские цифры:
0 1 2 3 4 5 6 7 8 9 A B C D E F G H J K L M N P R S T U V W X Y Z
Использовать буквы I, O, Q запрещено, так как они сходны по начертанию с цифрами 1, 0, а также между собой.
VIN состоит из 3 частей:
WMI (World Manufacturers Identification) — всемирный индекс изготовителя (1-3 символы)
VDS (Vehicle Description Section) — описательная часть (4-9 символы)
VIS (Vehicle Identification Section) — отличительная часть (10-17 символы)

Так вот в VIN коде есть символы которые несут информацию, а есть которые не несут (их обозначают "?")
например: WAU???4F?????????
WAU - "Audi AG", Felix Wankel Strasse, Neckarsulm, 74148, Germany
тип - 4F
или WBAW??1??????????
WBA - "Bayerische Motoren Werke AG", Muenchen 13, Germany
тип - X3

Позиции которые несут информацию у каждого ТС (транспортного средства) отличаются

как мне WAU???4F????????? сравнить с реальным VIN транспортного средства WAUZZZ4FXB002525?

вернее как организовать запись, поле, таблицу для "WAU???4F?????????" для дальнейшего сравнения?

Заранее благодарю за Ваше уделенное внимание и советы

LSV,
Возможно Вы правы, но без VIN: Вам не выдадут сертификат, не поставят на учет, не пройдете тех. осмотр
Это как ИНН у человека (есть исключения, люди отказываются), но без ИНН Вам никто ничего не захочет делать (официально)

Таблица КТС - это и есть карточка на определенное транспортное средство ("определенное" - известен VIN)
Остальные таблицы - "словари"

Но над неким логическим ключом вместо VIN подумаю.

Меня больше беспокоит, если оператор не будет знать тип, вариант, версию, номер одобрения типа, как с этим бороться? это ведь ключи.

Кот Матроскин,

Спасибо
Значит, просто делаю доп. таблицу
VIN C(16) ТИП С(40) Код производителя N(5)WBAW??1?????????? X3 10WAU???4F????????? 4F 5
а потом реальный VIN сравниваю через LIKE

softwarerLSVНе делайте ВИН ключом !!!
Пусть делает. Лучше один раз на собственной заднице почувствовать все прелести работы с естественными ключами, нежели всю жизнь страдать "я делаю как все, не пользуясь такой красивой идеей".

То есть, лучше совсем избегать естественные ключи?
Раньше так и делал (ключи были набор символов), но недавно наткнулся на 2 базы по своей теме и в обеих используются естественные ключи (номер одобрения, вариант, версия)
Поэтому решил попробовать. Вроде как проще будет делать запрос.

А. softwarerДа наздоровье. Просто, формулируя свои утверждения, стоит понимать, что изредка встречаются люди, использующие что-то другое :)

Где у меня сказано что int - зло?
RikitikiInt не всегда хорошо, пример Treeview - ключи начинаются с символа. Я сам часто использую ключи Int, особенно в таблицах - словарях. Или Вас смутило словосочетание "не всегда"?
Вы сами себе противоречите.
Если смотреть с Вашей "колокольни", я и есть человек, который "встречается изредка".
Или мне при населении объекта делать нечто - sqrt(int*24 - 100) и если число кратное 15, то вставлять вторым символом "А", а если нет, то первым "С". Бывают разные задачи. Иногда использую Int иногда смесь символов, цифр, дат, времени.
Вот и все, что я хотел сказать.
Б. По поводу ВИН.
Если ВИНа нет, инспектор на таможне не сможет определить марку, производителя ТС. Вы не сможете заплатить налог, Вам не пропустят на территорию государства. Если Вы прошли доп экспертизы (несколько кругов ада - "недешевого") и Вам написали, что ТС - без ВИН, то при прохождении сертификации Вам снова понадобятся "нервы" и деньги. "Шкурка выченки не стоит"
Поэтому для моей БД аксиома: "Вин - известен всегда" , иначе смысла в БД - нет
Если ВИН, по ошибке оператора вбит не правильно, поверьте, Вы заплатите еще раз и получите новый документ.
Заявку с ВИН подает владелец.

По поводу задания.
1. Упростить, упорядочить и организовать создание документов и облегчить их учет при сертификации КТС
2. Максимально минимизировать ошибки оператора (предложить выбор с нужными хар-ками)
3. При изменении в ТЗ, в максимально короткие сроки провести модернизацию БД.

Меня интересует правильность сущностей, таблиц и связей.

Это всего лишь часть БД, которая касается только КТС, без ЗАКАЗЧИКА, ИСПОЛНИТЕЛЯ, СУБПОДРЯДЧИКА, ИСПЫТАТЕЛЯ, ПРОТОКОЛОВ, НОРМАТИВНОЙ ДОКУМЕНТАЦИИ, ЖУРНАЛОВ

Описание
Производитель - производит одну или несколько марок ТС, имеет свой мировой идентификатор (WMI), который забит в ВИН (1-3 символы), правда нюанс, одному производителю, может соответствовать несколько кодов WMI, код WMI выдается на 50 лет, потом или продлевают или меняют, также название производителя может меняться (Мерседес-Бенц --- Даймлер-Бенц ----- Даймлеркраслер ---- а сейчас просто Даймлер)
создал отдельную таблицу "Производитель КТС"
Нужно ли отдельно делать таблицу названий, адресов или немного избыточности не помешает?
создал отдельную таблицу "Марка КТС"
связь "один ко многим" от "Производителя" к "Марке"
Каждая марка делится на несколько ТИП ов и Коммерческих названий . ТИПы - для производителя, коммерческие названия - для потребителя, но одному коммерческому названию может соответствовать несколько ТИП ов (Пример: коммерческое название Ауди А6 - ТИПы: 4Б, 4Ц, 4Ф и т.д.)
У ТИПа обязательным атрибутом является номер одобрения типа (каждый ТИП проходит одобрение хотя бы 1 раз в 5 лет) и некая маска ВИН (ВАК????Р???????). У коммерческого названия ограничений нет.
создал отдельную таблицу "Коммерческое название"
создал отдельную таблицу "ТИП"
связь "один ко многим" от "Марки" к "Коммерческому названию" и связь "один ко многим" от "Коммерческого названия" к "Типу".
нужна ли связь "один ко многим" от "Марки" к "Типу"?
ТИП может делится на варианты , а те в свою очередь на версии , но эта черта настолько расплывчата, что решил сделать одну таблицу для варианта и версии , снова немного избыточности.
создал отдельную таблицу "Базовая комплектация"
связь "один ко многим" от "Типу" к "Базовой комплектации"
Так вот номер одобрения , вариант и версия - это и есть база нашего КТС, зная ВИН и эти 3 параметра, можно рассказать все хар-ки КТС.
В свою очередь КТС имеет двигатель (кроме прицепов), а может и 2 двигателя ("гибриды"), поэтому таблица "Двигатель".
связь "один ко многим" от "Базовой комплектации" к "Двигателю"
Двигатель может быть одно, двух, трех ... топливными, поэтому таблица "Характеристики двигателя"
связь "один ко многим" от "Двигателя" к "Характеристики двигателя"
КТС имеет оси: одну, две, три ..., поэтому таблица "Ось"
связь "один ко многим" от "Базовой комплектации" к "Ось"
еще будет таблица "табличек ВИН" (располагаются минимум в 3 разных частях рамы), но она похожа с таблице "Ось"
Таблица КТС - это и есть главная таблица, куда оператор должен выбрать все хар-ки. Отличительные хар-ки это ВИН, цвет и дата выпуска, остальное нужно взять из таблиц, которые описаны выше или если нет, добавить эти характеристики вручную.

Описание процеса:
Клиент подал заявку с указание ВИН, топлива и объема двигателя. + Приложил "техпаспорт" если этот КТС б/у. Так вот, если в техпаспорте есть номер одобрения , вариант и версия - "бинго", все знаем, все умеем. Но есть техпаспорта где указана только марка или коммерческое название (ВИН - указан всегда), нужно максимально сузить параметры выбора для оператора, предложив ему всего пару вариантов, или пусть все вбивает сам (время, ошибки), проще проверить то, что предложила программа, чем самому заполнить 200 полей.
Исправленная схема в следующем сообщении

Структура

softwarerrikitikiГде у меня сказано что int - зло?
У Вас сказано, что treeview не воспринимает числовые ключи. И это очень смешно.

Кроме того, у Вас подразумевается замечательная логика "глюки клиента определяют архитектурные решения сервера", что как бы тоже смешно, но... вряд ли удастся объяснить, почему.
Я уже написал, что осознал свою ошибку с естественными ключами. Просто думал может новое веяние, дань моде. В схеме все исправил.
И хотелось бы услышать мнение именно по структуре, может нужно полностью избавиться от избыточности, или есть лишние связи, а может какой-то не хватает?
Спасибо.

s_ustinovrikitikiЕсли смотреть с Вашей "колокольни", я и есть человек, который "встречается изредка".
Или мне при населении объекта делать нечто - sqrt(int*24 - 100) и если число кратное 15, то вставлять вторым символом "А", а если нет, то первым "С" . Бывают разные задачи.
А можно подробнее про эту задачу?
Мне вот очень интересно, это в каких же задачах нужен ТАКОЙ искусственный ключ?
Простите если Вас это выражение оскорбило или задело.
Мне будет намного интересней Ваше мнение о структуре.
По поводу искусственных ключей я ведь прислушался к Вашему мнению. Если Вы заметили я даже не спорил, поскольку понял Вашу правоту.

s_ustinovЯ бы избавился от большей части ID производитель и коммерческое название - зачем оно почти во всех таблицах?
Я правильно понимаю в таблицах "Двигатель", "Ось", "Кузов", во всех после "Базовая комплектация"?

s_ustinovrikitikiпропущено...

Я правильно понимаю в таблицах "Двигатель", "Ось", "Кузов", во всех после "Базовая комплектация"?
Не только. Например, зачем производитель в колесное средство?
Это один из атрибутов ТС (я так понимаю как отчество у людей), этот атрибут необходим при формировании нескольких документов, но можно просто селектом при формировании док-та по первым трем символам ВИН это делать. Тогда и связь ТС-Производитель не нужна?

s_ustinovrikitiki,
Вот зачем здесь:
"Таблица КТС - это и есть главная таблица, куда оператор должен выбрать все хар-ки. Отличительные хар-ки это ВИН, цвет и дата выпуска , остальное нужно взять из таблиц, которые описаны выше или если нет, добавить эти характеристики вручную."
указывать производителя, тип, название и т.д.?
Как Вы предлагаете связать конкретный ВИН с остальными характеристиками?
Я это сделал с помощью ключей: idПроизводитель, idТип, idВар_Вер, idкоммерческое название
Я писал - название производителя может меняться (выше пример с Мерседесом)
У некоторых производителей нет вариантов и версий (мелкосерийное производство) один тип и все, без деления.
У разных производителей кодирование типа может совпадать (поэтому главный идентифицирующий атрибут в таблице ТИП это номер одобрения типа Пример: e1*2001/116*0254*23, если в ТИПе хар-ки поменялись не существенно и обозначение типа остается "старым" и через 5 лет производитель захочет и дальше выпускать этот ТИП номер будет e1*2001/116*0254* 24 )
е1 - страна, которая выдала одобрение, 2001/116 - европейская директива, 0254 - идентификационный номер типа, 23 - порядковый номер изменений.
Что Вы предлагаете оставить в таблице ТС?
Я повторю, идеальный вариант, если известно номер одобрения типа, вариант, версия


Вот, что должно получиться в конечном результате
https://www.google.com.ua/search?q=certificat de conformite&client=opera&hs=rS2&source=lnms&tbm=isch&sa=X&ved=0ahUKEwjY8ICRkPvRAhVlCpoKHU0pC3cQ_AUICCgB&biw=1280&bih=886&gws_rd=cr,ssl&ei=qz2YWJCtFqOC6AThmLCgCA


s_ustinovИ что значит "если нет, добавить эти характеристики вручную"? Вы представляете, что о вас скажет человек, которому придется анализировать эту "информацию", если в таблице будет тип или коммерческое название, введенные вручную? Если в справочниках нет нужного типа - надо создать новую запись в справочнике, а не плодить мусор. Другое дело, что могут быть ошибки в документах - название в "техпаспорте" не совпадает с названием, которое использует производитель. И вот для таких случаев надо предусмотреть отдельные поля.
Пардон за мой французский, это и значит "создать новую запись в справочнике"

Давайте смоделируем ситуацию.
Вы - оператор, я - заказчик и по совместительству Ваш сосед-друг. Поехал я в Германию, нашел автомобиль (цена-качество), но не уверен, что авто подойдет по классу выбросов (я не знаю какой класс ЕВРО 2, 3, 4, 5). В нашей стране действует регламент - завозить авто можно только авто классом ЕВРО не ниже 5. Звонить Вам дорого, смс - дешевле. Пишу Вам смс с информацией, которую вижу на авто ВИН (17 символов), дата выпуска, и коммерческое название. Капот закрыт и табличку с типом, вариантом и версией я не вижу. Прошу Вас помочь мне (баня с меня или бутылка). Вы по своей базе пробили, что согласно вин это производитель ВАСЯ, согласно коммерческого названия и производителя выбрали всевозможные ТИПЫ
1. и все предложенные варианты и версии оборудованы двигателями ЕВРО 5.
2. нужно обязательно поднять капот (машина стоит на стоянке в Германии, хозяин живет в Италии), поскольку в варианте ККЕНВЛК стоят двигателя ЕВРО 4. Друг уже думает, ждать ему хозяина или нет.
3. все авто оснащены двигателями ЕВРО 4.

Я утрировал ситуацию, но думаю принцип понятен.

s_ustinovID первичного ключа таблицы ТС, VIN и FK таблицы КТС.
Ну и еще ряд вспомогательных полей, но никаких дополнительных внешних ключей быть там не должно (из представленных на рисунке). Вы имели в ввиду ID первичного ключа таблицы ТС, VIN и FK таблицы базовая комплектация КТС
Если это так, я с Вами согласен
Если все таки FK таблицы КТС, тогда не понял