Hello,

I have the following structure of my database:

Table: Depot (ID_Depot, Name, City, Country)
Table: Route (ID_Route, ID_Origin_Depot, ID_Dest_Depot)

and would like to create a view like that:
Depot_Origin_Name, Depot_Origin_City, Depot_Dest_Name, Depot_Dest_City

how can I make it in mysql? Is such structure of database efficient
(what should I index to improve the performance)?

Thanks, Mark

mark wrote:
> Hello,
>
> I have the following structure of my database:
>
> Table: Depot (ID_Depot, Name, City, Country)
> Table: Route (ID_Route, ID_Origin_Depot, ID_Dest_Depot)
>
> and would like to create a view like that:
> Depot_Origin_Name, Depot_Origin_City, Depot_Dest_Name, Depot_Dest_City
>
> how can I make it in mysql?
CREATE VIEW mark_view AS
SELECT
o.Name Depot_Origin_Name,
o.City Depot_Origin_City,
d.Name Depot_Dest_Name,
d.City Depot_Dest_City
FROM Route r
JOIN Depot o ON r.ID_Origin_Depot = o.ID_Depot
JOIN Depot d ON r.ID_Origin_Depot = d.ID_Depot
WHERE r.ID_Route = 'route_ir'

> Is such structure of database efficient
It depends on what you want to do with it.

> (what should I index to improve the performance)?
It depends on what you want to do with it, although ID_Depot should be a
Primary Key

> Thanks, Mark
You're welcome