[thelist] SQL for what I think might be many to many

[rudy]

> However, if the names are unique enough, I'd suggest not
> using the IDs, but the names of the condos and beaches instead.

this is an excellent and strong suggestion

good one, ken

> That way you cut down on the traffic going to the db.

um, no, it's still just one call to the database, with the same number of
records in the result set (although there might be fewer columns in the
result set if you don't have ID numbers)

> In this above table, you'd have to make your SQL call to get the beaches
> within walking distance, then you'd have to make more SQL calls to get
> the names of the beaches and the condos based on the ID numbers the
> first call returned. That can get a bit slow depending on your DBMS,
server
> setup, and the algorithm used.

ay yi yi, the "algorithm" you just described sounds a lot like you're doing
multiple calls to the database!  like within a loop?!

yes, it's a many-to-many relationship, and, just as you suggested, john, a
keyed request starts on a single record on one side and looks for all
related record of the other side (given a beach, which condos are nearby?)
or vice versa in the other direction (given a condo, which beaches are
nearby?)

no matter which direction you're going, you use a 3-way join

one (1) call to the database

if anybody out there is doing things like not using ID numbers because
using names saves you from needless calls-within-a-loop to the database,
let me reassure you that a join is **always** going to outperform calling
the database in a loop

no offence, ken, and not to single you out or anything, but i just had to
say that

using the right query is a performance strategy of the first order, up
there along with making sure fields used in WHERE clauses are indexed, and
so on

i realize i sound like a commercial, sorry

second order optimizations are things like not designing a column as an
integer datatype when a smallint will do, or critically examining each
query

for instance, don't return ID fields

but by then you are facing diminishing returns for the time invested

which, in a very roundabout way, comes back to your suggestion

if the database is moderate in size (how many condos and beaches can there
be?) then it can make sense to completely dispense with ID numbers (because
then it's a join of two tables rather than three) but the chance of there
not being other attributes attached to the records on either side of the
m-m relationship is woefully small

sorry if that didn't make sense, let me know if i should expand


rudy