9 ms·

Tip for SQL users: If you give all your ID fields unique names, e.g. by calling your field "reservation_id" instead of "id", even in the reservation table, you

by mortehu 11y ago

Tip for SQL users: If you give all your ID fields unique names, e.g. by calling your field "reservation_id" instead of "id", even in the reservation table, you can do stuff like: SELECT * FROM reservation JOIN guest USING (reservation_id); By doing "USING (reservation_id)" instead of "ON reservation.id = guest.reservation_id", the field will be automatically deduplicated, so you don't have to qualify it elsewhere in the query, and "SELECT *" will return only one copy.

rlonstein 11y ago
But, but, but ActiveRecord doesn't do that by default so it can't be a good idea [sarcasm]
petepete 11y ago
Or if you're feeling lucky: SELECT * FROM reservation NATURAL INNER JOIN guest;
- jessedhillon 11y ago
  Err, be careful with natural joins: suppose both tables have a column called "name" or "created_at" -- natural join will create join conditions from those.
  - marcosdumay 11y ago
    I never understood why the thing named "natural join" don't use relationships to determine the joining columns. Does not look natural for me.
    - vog 11y ago
      Indeed, this is also what always annoyed me. I suspect that this has to do with the fact that NATURAL JOIN is intended to work for sub-SELECTs and VIEWs, too. On those, it is quite hard and error-prone to determine "foreign key" equivalents. Also note that restricting NATURAL JOINs to tables wouldn't be a good solution here, either, because that would prevent you from ever JOINing more than 2 tables: When JOINing 3 tables, you effectively do have some kind of sub SELECT after having joined the first two tables, which is then supposed to be joined with the third one.
      - marcosdumay 11y ago
        You get the same sort of problems jessedhillon was talking about. That is, you lose absolutely nothing - just changes its semantics.
  - MichaelBurge 11y ago
    That's why he said, "If you're feeling lucky".
    - sjwright 11y ago
      Which in the world of databases is a synonym for "useless". Typical boutique SQL query strings can be anything from a few to a few dozen lines and the compiled form is typically cached by the SQL engine (if you're using parameterised variables, which you are, because not doing so is stupid and dangerous). So why not be explicit?
Tenhundfeld 11y ago
I actually prefer just using id as the primary key, and I like the explicitness of seeing the table/alias before the column in complex queries. I don't care too much about typing it out; reservation.id isn't longer than reservation_id, and the savings of USING vs ON seem minimal. I also don't care about deduplicating that one field, as I'll likely need to consider other duplicate fields in the results, like created_at or description. But I can see arguments in both directions. I think the big thing is consistency. If you consistently use <table name>_id as the PK and your tooling doesn't fight it, then cool. I do feel like this benefit breaks down rather quickly though. For example, if you wanted to track who created a guest record, you probably wouldn't name the column user_id as it's ambiguous. So you might use creator_id to signify it points to the user who created the guest, not to the user invited to the reservation – thus negating your ability to join to a user table via USING. That's not to say it's a bad tip, just because there are cases where it doesn't fit. Definitely worth knowing. Thanks for sharing.
- jessedhillon 11y ago
  When you have to join on composite PKs, it's easier to appreciate the parent's advice.
  - Tenhundfeld 11y ago
    Yeah, I hear that. I also tend to always use surrogate keys. It can have performance implications, but that's typically not my biggest concern. EDIT: I guess that doesn't really address your point of needing to join on a composite natural key though.
    - ahachete 11y ago
      Do you know SSSKA? Society to Stop Surrogate Key Abuse: http://www.slideshare.net/PGExperts/keyvil-lightning-talk?qid=79815443-4c03-4119-937c-a001ef338a13 http://www.slideshare.net/PGExperts/keyvil-lightning-talk?qi... ;)
      - 11y ago
ciroduran 11y ago
And if you're using a schema you did not create, don't forget to use shorthands! SELECT * FROM reservation r JOIN guest g ON (r.id = g.reservation_id), queries can get messy if you don't use them! :)
leepowers 11y ago
I prefer longer, more descriptive table names coupled with shorter columns names. Then use aliases when writing queries. SELECT * FROM long_table_name l LEFT JOIN another_table_here a ON l.id = a.rel_id Also, most tables end up being used to populate objects. It's simpler to reference an object with `Reservation.id` than `Reservation.reservation_id` Regardless of the design choices made it's more important to be consistent in naming conventions and to document why a particular design/schema was chosen.
- developer2 11y ago
  I tabbed away from my previous comment for a minute, which made me forget to mention the ORM scheme in applications. You're exactly right. Nobody ever uses the class "Reservation" with a property named "reservation_id" in their code. They will name their column "reservation_id" in the database, but then work to undo that at the application level by telling the ORM to map "reservation_id" to an object property named "id". It creates the situation where developers fight against the database schema in their code, and adds an unnecessary level of complexity when comparing code to database. "Why is it 'id' in code but 'reservation_id' in the database?". A common counterargument from pure DBAs, who are not themselves writing code that integrates with the schemas they create, is that databases should be approached as a completely standalone component that should not be designed with applications' structures in mind. That applications are free to bastardize the database schema in any way they please, so it really doesn't matter anyway. I personally prefer to see it as a reality that applications will be the primary consumer of the database resource, and deserve to have their intended integrations analyzed. Once the database is finalized, it is the application developers who will be studying the schema on a weekly basis. Anything to ease that constant inspection process, the better. Of course, this is all personal preference. I have learned over the years to finally stop arguing so much over these little details. In real life with coworkers, anyway. It's still fun to battle for your point of view with forum strangers! :D
- whitenoice 11y ago
  The only problem I have with this is that when you have a lot of joins and your query is long it gets confusing, as in what table was 'l', what table was 'a' and then you have to scroll up and look it up again, whereas in the parent comment its easy as it makes it explicitly clear and the table name does not matter when looking at joins.
developer2 11y ago
Not really a fan. When your table name is "reservation", you're prefixing "id" with the table name ("reservation_id"), but you don't do the same for the other columns. I've never liked the inconsistency of having "reservation_id", but other columns like "name" instead of "reservation_name". Especially on longer table names where you wind up with columns like "this_really_long_table_id". All just to shorten join clauses with a natural join or USING? You already get shortened joins - less so, but still a gain - by using table aliases so as not to repeat the long table names in each query multiple times. IMO it also takes an extra moment to parse the ambiguous "id" columns to resolve them mentally to which id is being referred to.
- Mahn 11y ago
  Personally, for me it makes sense that the id field has a different distintive name for each table because it's a specific kind of id, i.e., customerID is not interchangeable with reservationID. For me, this is not so much about prepending the field with the table name, but about calling things by their name. "id" is just too vague, even if you use it in the context of a specific table.
  - fleitz 11y ago
    I believe this is what foreign keys are for, and also, they are usually both ints/uuids/etc so generally they are interchangeable. I fail to see how customer.id = product.id is any less obvious of a bug than customer.customerID = product.productID
    - jessaustin 11y ago
      With aliases that first expression might be: c.id = p.id That seems like an easy error to make...
      - liotier 11y ago
        > With aliases that first expression might be: c.id = p.id Which is why I don't use aliases, so that future me doesn't have to wonder what the hell he wrote a few months ago.
swah 11y ago
Another option: SELECT * FROM reservation r JOIN guest USING (r.id);
daxelrod 11y ago
It's long bothered me that SQL doesn't have a way of saying "join these two tables based on the key relationships I've already defined in my schema". (Or maybe some variants do?) The database already knows that guest.reservation_id is a foreign key referencing reservation.id. Why should you have to repeat yourself?
- greggyb 11y ago
  And when there are multiple foreign keys between two tables?
  - kbsletten 11y ago
    Genuinely curious, what's the use-case for this?
    - cname 11y ago
      created_by and updated_by both pointing at the user table?
    - slagfart 11y ago
      Explicit foreign keys perform better in some joins, as the optimiser can assume that all possible values are in the source column of that foreign key.
    - level3 11y ago
      Some examples: In project management, tasks might have an assigner_id and assignee_id In transaction management, transactions might have a sender_id and receiver_id In sports, matches might have a team1_id and team2_id
      - jonahx 11y ago
        > In transaction management, transactions might have a sender_id and receiver_id in normal form, shouldn't you have "transactions", "transaction_senders", and "transaction_receivers" anyway? similarly for the other cases? i know sometimes the simplicity of a single table is a valid choice, so your point still stands, but it does feel like the examples are cases of "well, if you're were doing the right way(TM), that wouldn't happen"
sokoloff 11y ago
I don't like using select * as it will cause the shape of the result set to change upon schema modifications. We use a DB access layer that "knows" the column indices of result sets at compile time and our zero-downtime process relies on that not changing (for the time period where old code is running against new schema). Imagine two two-column tables, A with id and name, and B with id and value. (Doesn't matter what the columns are; this is just for concreteness.) select * from A JOIN B using (id) will give you a three column result set, id, name, value. Now, suppose you want to do a schema modification to add "last _login" to A. Suddenly, your query is returning a 4 column result set, id, name, last_login, value and code using ordinal positions is now reading last_login into the "value" slot. Maybe it's a corner-case for our use case and zero-downtime release processes, but it's something to be aware of anyway.
- level3 11y ago
  I'm curious: what are the advantages of having your access layer rely on column indices rather than column names?
  - sokoloff 11y ago
    At coding-time (pre-compilation), we create classes representing each stored procedure that we've marked for code generation. That gives parameter list (inc auto-completion in the IDE), column names, types/type safety on inputs and outputs, etc, from the parameter list and result set shape and it was [slightly] easier to write the generic code that binds columns in the result set by column index, since we "knew" the column indices wouldn't change in the result set. (We had a prior system that relied on column order and so had already banned select * and only allowed additions to the result set on the right side before we even got to the point of doing codegen or zero-downtime releases. This was 2003 that we created the codegen wrapper and 2005 or so when we switched to zero downtime releases.) I guess "because we're lazy" is the somewhat overly glib answer. ;-)
- lccarrasco 11y ago
  Most drivers or interfaces to the db let you use the column's name instead of the ordinal positions, precisely because that decouples the query's result from the things you care about, might be worth checking ^^
voltagex_ 11y ago
In which dialect?
- ciroduran 11y ago
  It's part of the SQL-92 standard http://flylib.com/books/en/4.343.1.38/1/ http://flylib.com/books/en/4.343.1.38/1/
ComodoHacker 11y ago
> the field will be automatically deduplicated, so you don't have to qualify it elsewhere in the query, and "SELECT " will return only one copy. You still have in outer joins. Also, SELECT * is a bad practice (with some exceptions) when used in code, suitable only for ad-hoc queries.
- huuu 11y ago
  Agreed. Or at least put a table name in front of the asterisk: SELECT table1.*, table2.field FROM table1 JOIN table2 ON table2.rel_id = table1.id
  - ComodoHacker 11y ago
    table.* doesn't really do any better. Database evolves, table's structure changes and you get various bugs and unpredictable behavior. From elusive exceptions to memory hogs to network bottlenecks, depending on DBMS, data itself and data access framework you use.
    - huuu 11y ago
      It does better when, for example, you have a field called 'id' in both tables. SELECT * FROM table1 JOIN table2 ON table2.rel_id = table1.id will use one of the 'id' field from table1 or table2 (depending on the join type). SELECT table1.* FROM table1 JOIN table2 ON table2.rel_id = table1.id will use the 'id' field of table1 and does not include data from table2. But I agree with your other arguments.
NicoJuicy 11y ago
I like using id as key, if you want to "decouple" info, i just use reservation.id as reservation_id This way, my db fetch is always on id and not a longer name ( reservation_id), which on the end of an webapp, makes me save some reading / writing :)
piers 11y ago
I really hope you're using SELECT * as an example and that you don't really do that.
rusanu 11y ago
I don't like this because it leads to the misleading assumption that it doesn't matter which value you refer (left or right), since they are equal. But once you consider collations is easy to see the fallacy: left.id = 'Something' COLLATE case insensitive right.id = 'SOMETHING' COLLATE case insensitive SELECT id FROM left JOIN right USING (id); Will the value be 'Something' or 'SOMETHING'? Better avoid the surprise and be specific...

by mortehu 11y ago

rlonstein 11y ago

But, but, but ActiveRecord doesn't do that by default so it can't be a good idea [sarcasm]

petepete 11y ago

Or if you're feeling lucky: SELECT * FROM reservation NATURAL INNER JOIN guest;

jessedhillon 11y ago
Err, be careful with natural joins: suppose both tables have a column called "name" or "created_at" -- natural join will create join conditions from those.
- marcosdumay 11y ago
  I never understood why the thing named "natural join" don't use relationships to determine the joining columns. Does not look natural for me.
  - vog 11y ago
    Indeed, this is also what always annoyed me. I suspect that this has to do with the fact that NATURAL JOIN is intended to work for sub-SELECTs and VIEWs, too. On those, it is quite hard and error-prone to determine "foreign key" equivalents. Also note that restricting NATURAL JOINs to tables wouldn't be a good solution here, either, because that would prevent you from ever JOINing more than 2 tables: When JOINing 3 tables, you effectively do have some kind of sub SELECT after having joined the first two tables, which is then supposed to be joined with the third one.
    - marcosdumay 11y ago
      You get the same sort of problems jessedhillon was talking about. That is, you lose absolutely nothing - just changes its semantics.
- MichaelBurge 11y ago
  That's why he said, "If you're feeling lucky".
  - sjwright 11y ago
    Which in the world of databases is a synonym for "useless". Typical boutique SQL query strings can be anything from a few to a few dozen lines and the compiled form is typically cached by the SQL engine (if you're using parameterised variables, which you are, because not doing so is stupid and dangerous). So why not be explicit?

Tenhundfeld 11y ago

I actually prefer just using id as the primary key, and I like the explicitness of seeing the table/alias before the column in complex queries. I don't care too much about typing it out; reservation.id isn't longer than reservation_id, and the savings of USING vs ON seem minimal. I also don't care about deduplicating that one field, as I'll likely need to consider other duplicate fields in the results, like created_at or description. But I can see arguments in both directions. I think the big thing is consistency. If you consistently use <table name>_id as the PK and your tooling doesn't fight it, then cool. I do feel like this benefit breaks down rather quickly though. For example, if you wanted to track who created a guest record, you probably wouldn't name the column user_id as it's ambiguous. So you might use creator_id to signify it points to the user who created the guest, not to the user invited to the reservation – thus negating your ability to join to a user table via USING. That's not to say it's a bad tip, just because there are cases where it doesn't fit. Definitely worth knowing. Thanks for sharing.

jessedhillon 11y ago
When you have to join on composite PKs, it's easier to appreciate the parent's advice.
- Tenhundfeld 11y ago
  Yeah, I hear that. I also tend to always use surrogate keys. It can have performance implications, but that's typically not my biggest concern. EDIT: I guess that doesn't really address your point of needing to join on a composite natural key though.
  - ahachete 11y ago
    Do you know SSSKA? Society to Stop Surrogate Key Abuse: http://www.slideshare.net/PGExperts/keyvil-lightning-talk?qid=79815443-4c03-4119-937c-a001ef338a13 http://www.slideshare.net/PGExperts/keyvil-lightning-talk?qi... ;)
    - 11y ago

ciroduran 11y ago

And if you're using a schema you did not create, don't forget to use shorthands! SELECT * FROM reservation r JOIN guest g ON (r.id = g.reservation_id), queries can get messy if you don't use them! :)

leepowers 11y ago

I prefer longer, more descriptive table names coupled with shorter columns names. Then use aliases when writing queries. SELECT * FROM long_table_name l LEFT JOIN another_table_here a ON l.id = a.rel_id Also, most tables end up being used to populate objects. It's simpler to reference an object with `Reservation.id` than `Reservation.reservation_id` Regardless of the design choices made it's more important to be consistent in naming conventions and to document why a particular design/schema was chosen.

developer2 11y ago
I tabbed away from my previous comment for a minute, which made me forget to mention the ORM scheme in applications. You're exactly right. Nobody ever uses the class "Reservation" with a property named "reservation_id" in their code. They will name their column "reservation_id" in the database, but then work to undo that at the application level by telling the ORM to map "reservation_id" to an object property named "id". It creates the situation where developers fight against the database schema in their code, and adds an unnecessary level of complexity when comparing code to database. "Why is it 'id' in code but 'reservation_id' in the database?". A common counterargument from pure DBAs, who are not themselves writing code that integrates with the schemas they create, is that databases should be approached as a completely standalone component that should not be designed with applications' structures in mind. That applications are free to bastardize the database schema in any way they please, so it really doesn't matter anyway. I personally prefer to see it as a reality that applications will be the primary consumer of the database resource, and deserve to have their intended integrations analyzed. Once the database is finalized, it is the application developers who will be studying the schema on a weekly basis. Anything to ease that constant inspection process, the better. Of course, this is all personal preference. I have learned over the years to finally stop arguing so much over these little details. In real life with coworkers, anyway. It's still fun to battle for your point of view with forum strangers! :D
whitenoice 11y ago
The only problem I have with this is that when you have a lot of joins and your query is long it gets confusing, as in what table was 'l', what table was 'a' and then you have to scroll up and look it up again, whereas in the parent comment its easy as it makes it explicitly clear and the table name does not matter when looking at joins.

developer2 11y ago

Not really a fan. When your table name is "reservation", you're prefixing "id" with the table name ("reservation_id"), but you don't do the same for the other columns. I've never liked the inconsistency of having "reservation_id", but other columns like "name" instead of "reservation_name". Especially on longer table names where you wind up with columns like "this_really_long_table_id". All just to shorten join clauses with a natural join or USING? You already get shortened joins - less so, but still a gain - by using table aliases so as not to repeat the long table names in each query multiple times. IMO it also takes an extra moment to parse the ambiguous "id" columns to resolve them mentally to which id is being referred to.

Mahn 11y ago
Personally, for me it makes sense that the id field has a different distintive name for each table because it's a specific kind of id, i.e., customerID is not interchangeable with reservationID. For me, this is not so much about prepending the field with the table name, but about calling things by their name. "id" is just too vague, even if you use it in the context of a specific table.
- fleitz 11y ago
  I believe this is what foreign keys are for, and also, they are usually both ints/uuids/etc so generally they are interchangeable. I fail to see how customer.id = product.id is any less obvious of a bug than customer.customerID = product.productID
  - jessaustin 11y ago
    With aliases that first expression might be: c.id = p.id That seems like an easy error to make...
    - liotier 11y ago
      > With aliases that first expression might be: c.id = p.id Which is why I don't use aliases, so that future me doesn't have to wonder what the hell he wrote a few months ago.

swah 11y ago

Another option: SELECT * FROM reservation r JOIN guest USING (r.id);

daxelrod 11y ago

It's long bothered me that SQL doesn't have a way of saying "join these two tables based on the key relationships I've already defined in my schema". (Or maybe some variants do?) The database already knows that guest.reservation_id is a foreign key referencing reservation.id. Why should you have to repeat yourself?

greggyb 11y ago
And when there are multiple foreign keys between two tables?
- kbsletten 11y ago
  Genuinely curious, what's the use-case for this?
  - cname 11y ago
    created_by and updated_by both pointing at the user table?
  - slagfart 11y ago
    Explicit foreign keys perform better in some joins, as the optimiser can assume that all possible values are in the source column of that foreign key.
  - level3 11y ago
    Some examples: In project management, tasks might have an assigner_id and assignee_id In transaction management, transactions might have a sender_id and receiver_id In sports, matches might have a team1_id and team2_id
    - jonahx 11y ago
      > In transaction management, transactions might have a sender_id and receiver_id in normal form, shouldn't you have "transactions", "transaction_senders", and "transaction_receivers" anyway? similarly for the other cases? i know sometimes the simplicity of a single table is a valid choice, so your point still stands, but it does feel like the examples are cases of "well, if you're were doing the right way(TM), that wouldn't happen"

sokoloff 11y ago

I don't like using select * as it will cause the shape of the result set to change upon schema modifications. We use a DB access layer that "knows" the column indices of result sets at compile time and our zero-downtime process relies on that not changing (for the time period where old code is running against new schema). Imagine two two-column tables, A with id and name, and B with id and value. (Doesn't matter what the columns are; this is just for concreteness.) select * from A JOIN B using (id) will give you a three column result set, id, name, value. Now, suppose you want to do a schema modification to add "last _login" to A. Suddenly, your query is returning a 4 column result set, id, name, last_login, value and code using ordinal positions is now reading last_login into the "value" slot. Maybe it's a corner-case for our use case and zero-downtime release processes, but it's something to be aware of anyway.

level3 11y ago
I'm curious: what are the advantages of having your access layer rely on column indices rather than column names?
- sokoloff 11y ago
  At coding-time (pre-compilation), we create classes representing each stored procedure that we've marked for code generation. That gives parameter list (inc auto-completion in the IDE), column names, types/type safety on inputs and outputs, etc, from the parameter list and result set shape and it was [slightly] easier to write the generic code that binds columns in the result set by column index, since we "knew" the column indices wouldn't change in the result set. (We had a prior system that relied on column order and so had already banned select * and only allowed additions to the result set on the right side before we even got to the point of doing codegen or zero-downtime releases. This was 2003 that we created the codegen wrapper and 2005 or so when we switched to zero downtime releases.) I guess "because we're lazy" is the somewhat overly glib answer. ;-)
lccarrasco 11y ago
Most drivers or interfaces to the db let you use the column's name instead of the ordinal positions, precisely because that decouples the query's result from the things you care about, might be worth checking ^^

voltagex_ 11y ago

In which dialect?

ciroduran 11y ago
It's part of the SQL-92 standard http://flylib.com/books/en/4.343.1.38/1/ http://flylib.com/books/en/4.343.1.38/1/

ComodoHacker 11y ago

> the field will be automatically deduplicated, so you don't have to qualify it elsewhere in the query, and "SELECT " will return only one copy. You still have in outer joins. Also, SELECT * is a bad practice (with some exceptions) when used in code, suitable only for ad-hoc queries.

huuu 11y ago
Agreed. Or at least put a table name in front of the asterisk: SELECT table1.*, table2.field FROM table1 JOIN table2 ON table2.rel_id = table1.id
- ComodoHacker 11y ago
  table.* doesn't really do any better. Database evolves, table's structure changes and you get various bugs and unpredictable behavior. From elusive exceptions to memory hogs to network bottlenecks, depending on DBMS, data itself and data access framework you use.
  - huuu 11y ago
    It does better when, for example, you have a field called 'id' in both tables. SELECT * FROM table1 JOIN table2 ON table2.rel_id = table1.id will use one of the 'id' field from table1 or table2 (depending on the join type). SELECT table1.* FROM table1 JOIN table2 ON table2.rel_id = table1.id will use the 'id' field of table1 and does not include data from table2. But I agree with your other arguments.

NicoJuicy 11y ago

I like using id as key, if you want to "decouple" info, i just use reservation.id as reservation_id This way, my db fetch is always on id and not a longer name ( reservation_id), which on the end of an webapp, makes me save some reading / writing :)

piers 11y ago

I really hope you're using SELECT * as an example and that you don't really do that.

rusanu 11y ago

I don't like this because it leads to the misleading assumption that it doesn't matter which value you refer (left or right), since they are equal. But once you consider collations is easy to see the fallacy: left.id = 'Something' COLLATE case insensitive right.id = 'SOMETHING' COLLATE case insensitive SELECT id FROM left JOIN right USING (id); Will the value be 'Something' or 'SOMETHING'? Better avoid the surprise and be specific...