#sql #база_данных
Всем привет. Как правильно связать левую таблицу с группой из 3ех таблиц справа?
Только вот получается связать методом представленным на 2ом изображении
Например, пользователь выбирает конкретную марку, модель и поколение автомобиля,
и его выбор (марка, модель, поколение) требуется записать в другую таблицу.
create table CarBrend(
[BrendID] int identity (0,1) not null,
[Title] nvarchar(max) not null,
constraint PK_BrendID primary key ([BrendID])
)
create table CarModel(
[ModelID] int identity (0,1) not null,
[BrendID] int not null foreign key references CarBrend([BrendID]),
[Title] nvarchar(max) not null,
constraint PK_ModelID primary key ([ModelID])
)
create table CarGeneration(
[GenerationID] int identity (0,1) not null,
[ModelID] int not null foreign key references CarModel([ModelID]),
[Title] nvarchar(max) not null,
[StartProduction] date not null,
[EndProduction] date not null,
constraint PK_GenerationID primary key ([GenerationID])
)
create table Car(
[CarID] int identity (0,1) not null,
[BrendID] int not null foreign key references CarBrend([BrendID]),
[ModelID] int not null foreign key references CarModel([ModelID]),
[GenerationID] int not null foreign key references CarGeneration([GenerationID]),
constraint PK_CarID primary key ([CarID]),
)
Я сомневаюсь в правильности 4ой таблицы (Car). Подскажите, как правильно поступить
в данном случае.
В первой таблице CarBrend содержится марка автомобиля Nissan (и другие марки).
В второй таблице CarModel содержится модель Primera марки Nissan (и другие модели).
В третьей таблице CarGeneration содержаться все поколения Nissan'а Primer'ы - P10,
P11-120, P11-140, P12 (и других).
А в таблице Car(4) объединяются записи со всех 3х предыдущих таблиц (чтобы сформировать
полную машину).
Ответы
Ответ 1
БД не должна позволять внести в себя противоречивые данные. Это основной принцип проектирования. Второй предложенный вами способ, т.е с тремя полями у машины, позволяет создать одну машину бренда Мерседес и конкретной модели Mercedes W140 и другую машину, так же модель Mercedes W140, но поставить ей бренд Жигули. Значит такая БД сама напрашивается на внесение в нее некорректных данных. Второй принцип проектирования - простота изменения одной сущности. предположим мы ошиблись и отнесли модель W140 к бренду жигули. создали несколько таких машин, спохватились и решили исправить бренд. В первом случае нам надо только менять бренд у конкретной модели. А во втором еще и пробежаться по всем машинам и поменять бренд у них. Если посмотреть теорию проектирования, то второй вариант, с тремя ссылками, нарушает третью нормальную форму. Следовательно у машины должна быть только ссылка на ревизию модели и более ни на что. это позволит как избежать внесения несогласованных данных так и обеспечит принцип изменения одной сущности в одной таблице. Так что верна ваша первая картинка.Ответ 2
Вообще говоря, ваша таблица Car нарушает третью нормальную форму - поскольку в ней есть неключевая зависимость GenerationID -> ModelID -> BrendID. С точки зрения теории реляционных БД, в этой таблице достаточно одного атрибута GenerationID, а ModelID и BrendID избыточны. Это могло бы привести к т.н. аномалиям вставки, обновления и удаления (термины для гугления), не будь у вас таблиц CarModel и CarGeneration. Но и в текущем виде не все так хорошо: ваша БД не застрахована от некорректных данных. Мелкая ошибка в коде, и у вас будут Жигули модели Mercedes W140 (спасибо Mike за пример). Однако, слепо убирать эти поля тоже неправильно. Они тут делают важную вещь - ускоряют поиск. Предположим, что эти поля убраны из таблицы Car, а нам надо найти все машины заданного бренда. В таком случае запрос будет выполняться примерно так: Получение всех моделей заданного бренда Получение всех поколений для каждой модели Получение всех машин каждого поколения Два вложенных цикла для такого простого запроса! И никак его не ускорить индексами - потому что индексы нельзя строить по нескольким таблицам... Теперь как надо делать. Вариант 1 - использование составных внешних ключей Этот способ позволяет добавить валидацию данных, делая вставку "странных" автомобилей невозможной. Делается это так: create table Car( CarID int identity (0,1) not null primary key, BrendID int not null, ModelID int not null, GenerationID int not null, foreign key (BrendID) references CarBrend(BrendID), foreign key (ModelID, BrendID) references CarModel(ModelID, BrendID), foreign key (GenerationID, ModelID) references CarGeneration(GenerationID, ModelID), ) Не забудьте добавить индексы, потому что без них подобное усложнение структуры не имеет смысла. Вариант 2 - использование индексированных представлений (только для MS SQL) Можно из исходной таблицы эти атрибуты удалить, но создать индексированное представление, которое будет ускорять поиск: create view CarView with schemabinding as select Car.CarID, CarModel.BrendID, CarGeneration.ModelID, Car.GenerationID from Car inner join CarGeneration on Car.GenerationID = CarGeneration.GenerationID inner join CarModelon CarGeneration.ModelID = CarModel.ModelID go create unique nonclustered index PK_CarView on CarView(CarID); create index IX_CarView_BrendID on CarView(BrendID); create index IX_CarView_ModelID on CarView(ModelID); create index IX_CarView_GenerationID on CarView(GenerationID); В Enterprise-редакции MS SQL Server само наличие этого представления будет ускорять поиск автомобилей по бренду и модели (фактически, в этой редакции индексированное представление позволяет создавать те самые индексы по нескольким таблицам). В младших же редакциях требуется делать явный запрос к представлению с ключом NOEXPAND: select * from CarView WITH(NOEXPAND) where BrendID=5 Если генерация таких запросов невозможна из-за ограничений ORM, это можно обойти еще одним представлением: create view CarView2 as select * from CarView WITH(NOEXPAND);Ответ 3
Мне кажется вы все правильно связываете, все связи вроде бы правильно установлены.Ответ 4
Мне кажется, что связи между правыми тремя таблицами избыточны, вполне достаточно левой таблицы, она связывает между собой три правых. Да, и в левой можно объединить 3 форинключа в однин первичный, хотя может быть, так лучше и не делать, точнее это зависит от требований уникальности.Ответ 5
Ваша архитектура усложнена куда-то на уровень полетов в космос, у вас столько таблиц, а здесь по сути хватит 2 таблиц, car_brand и car_model, все остальное лишнее дублирование. Какую нагрузку несет таблица Car вообще непонятно, это как таблица CarModel + поле GenerationId. В вашем случае, модель обязательно имеет дату производства и дату конца производства, почему она в отдельной таблице? Это поле относящееся к записи непосредственно и ее нужно вставить в таблицу car_model. Рекомендую использовать наиболее используемый кейс наименования таблиц snake case. Желательно поля brandId, modelId переименовать просто в id, чтобы исключить лишнее дублирование в названии колонок, хотя это конечно дело вкуса. Используем один ключ: car_model.brand_id = car_brand.id
Комментариев нет:
Отправить комментарий