Database Design.

numnutz

numnutz

numnutz

Associate
Joined
18 Oct 2002
Posts
761
Location
Berkshire
Never been a massive fan of DB design, but I've got a fairly simple requirement for data that I'd like to implement correctly.

It's a lookup for Tube Stations, my challenge is how to manage the fact a tube station can belong to more than one line?

My normal approach, if a Tube station had only one line would be to use a foreign key to join two tables. Do I need a third table that simply manages the relationship between tube station and line(s)

Thanks
 
I am equally as disinterested in DB design, so in my laymans approach (and correct if wrong DB pros).

One line will have many tube stations.
One tube station can have many lines.

Therefore, many tube stations can have many lines. And a many to many relationship requires a joining table.

Beyond that I get hazy. I think you have unique IDs for stations and lines and then the joining table says station 1 has lines 2, 5, 6 running through it. Station 2 may have lines 2, 3, 5.
 
Just a tip: Let the design of your application dictate the DB design, not the other way round.

As above though, in a many-to-many relationship, you'll need a link table.
 
Might be the wrong way to do it but I'd have 3 tables.

Lines table
line_id, name, whatever
1, line1, whatever
2, line2, whatever

Stations table
station_id, name, whatever
1, station1, whatever
2, station2, whatever
3, station3, whatever

Station_lines table
station_id, line_id
1, 2
1, 3
1, 1
2, 1
3, 1
3, 2

Then if you want the lines for station2
"select line_id from station_lines where station_id = 3"

Or if you want the stations for line1
"select station_id from station_lines where line_id = 1"

And to get the info for each line for example:

"select station_lines.line_id, lines.name, lines.whatever from station_lines, lines where station_lines.station_id = 3 AND station_lines.line_id = lines.line_id"

Clear as mud.
 
That last query is not needed. If you want line info, no need to bring station_lines into it. Just do:
Code: 
select * from lines

If you want line info for all lines at a given station:
Code: 
select lines.* from lines 
  inner join station_lines 
    on lines.id = station_lines.line_id
  where station_lines.station_id = @stationid
 
i would add an order column on the link table, as you won't be able to guarantee that they're in the order on the line when inserted into the database.
 
Dj_Jestar said:
Just a tip: Let the design of your application dictate the DB design, not the other way round.

As above though, in a many-to-many relationship, you'll need a link table.
Click to expand...

I've seen the results of that attitude far too many times. It's not pretty.

Design the database properly, and have some objects that interact with the data that fits your application.
 
sldsmkd said:
I've seen the results of that attitude far too many times. It's not pretty.

Design the database properly, and have some objects that interact with the data that fits your application.
Click to expand...

That is a fool's thought and a Victim of Cargo Cult, if I ever did see one. I too have seen it many times, and it's very pretty. A lot more pretty than seeing someone design the DB upfront, then (everytime) realise that it's wrong but "too late to change".

The DB is there to support the application. The application is not there to support the DB.

Top-down design > Bottom-up design.
 
Last edited:
Dj_Jestar said:
That is a fool's thought and a Victim of Cargo Cult, if I ever did see one. I too have seen it many times, and it's very pretty. A lot more pretty than seeing someone design the DB upfront, then (everytime) realise that it's wrong but "too late to change".

The DB is there to support the application. The application is not there to support the DB.

Top-down design > Bottom-up design.
Click to expand...

We're probably talking at cross purposes here. I have too much experience of coming into projects where the original development team didn't understand what a RDBMS a) is, b) what problems it solves and c) how to use it.

It leads to databases with 10 million records, each with a serialized representation of an object and all of the relationships hard baked into the objects. All of them have to be loaded at the same time by the application before it's useful

You can't tune the performance, and the user goes nuts because something that should take 2 seconds takes 30 minutes because the network is bottlenecked.

Database tables aren't immutable, and a decent abstraction layer will handle it.
 
Looks like we are talking cross-purposes. I'm not advocating you serialise objects into a table. Far from it.

I'm merely pointing out that relationships between objects should dictate the design of the database, not the relationships of the tables dictate the objects. :)

DBs aren't immutable, but require a massive effort to change once implemented and deployed. Usually far more effort than the product owner is going to permit for what is going to be an inferior return in value.
 
Dj_Jestar said:
Looks like we are talking cross-purposes. I'm not advocating you serialise objects into a table. Far from it.

I'm merely pointing out that relationships between objects should dictate the design of the database, not the relationships of the tables dictate the objects. :)

DBs aren't immutable, but require a massive effort to change once implemented and deployed. Usually far more effort than the product owner is going to permit for what is going to be an inferior return in value.
Click to expand...

I agree, code shouldn't be contorted around database structure, and vice versa. Like I said a bit of abstraction goes a long way.
 
You must log in or register to reply here.
Back
Top Bottom