<story><title>Design better databases</title><url>https://web.archive.org/web/20160408103107/http://dbpatterns.com/</url></story><parent_chain><item><author>mortehu</author><text>Tip for SQL users:&lt;p&gt;If you give all your ID fields unique names, e.g. by calling your field &amp;quot;reservation_id&amp;quot; instead of &amp;quot;id&amp;quot;, even in the reservation table, you can do stuff like:&lt;p&gt;SELECT * FROM reservation JOIN guest USING (reservation_id);&lt;p&gt;By doing &amp;quot;USING (reservation_id)&amp;quot; instead of &amp;quot;ON reservation.id = guest.reservation_id&amp;quot;, the field will be automatically deduplicated, so you don&amp;#x27;t have to qualify it elsewhere in the query, and &amp;quot;SELECT *&amp;quot; will return only one copy.</text></item></parent_chain><comment><author>Tenhundfeld</author><text>I actually prefer just using id as the primary key, and I like the explicitness of seeing the table&amp;#x2F;alias before the column in complex queries. I don&amp;#x27;t care too much about typing it out; reservation.id isn&amp;#x27;t longer than reservation_id, and the savings of USING vs ON seem minimal. I also don&amp;#x27;t care about deduplicating that one field, as I&amp;#x27;ll likely need to consider other duplicate fields in the results, like created_at or description.&lt;p&gt;But I can see arguments in both directions. I think the big thing is consistency. If you consistently use &amp;lt;table name&amp;gt;_id as the PK and your tooling doesn&amp;#x27;t fight it, then cool.&lt;p&gt;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&amp;#x27;t name the column user_id as it&amp;#x27;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.&lt;p&gt;That&amp;#x27;s not to say it&amp;#x27;s a bad tip, just because there are cases where it doesn&amp;#x27;t fit. Definitely worth knowing. Thanks for sharing.</text></comment>
<story><title>Design better databases</title><url>https://web.archive.org/web/20160408103107/http://dbpatterns.com/</url></story><parent_chain><item><author>mortehu</author><text>Tip for SQL users:&lt;p&gt;If you give all your ID fields unique names, e.g. by calling your field &amp;quot;reservation_id&amp;quot; instead of &amp;quot;id&amp;quot;, even in the reservation table, you can do stuff like:&lt;p&gt;SELECT * FROM reservation JOIN guest USING (reservation_id);&lt;p&gt;By doing &amp;quot;USING (reservation_id)&amp;quot; instead of &amp;quot;ON reservation.id = guest.reservation_id&amp;quot;, the field will be automatically deduplicated, so you don&amp;#x27;t have to qualify it elsewhere in the query, and &amp;quot;SELECT *&amp;quot; will return only one copy.</text></item></parent_chain><comment><author>leepowers</author><text>I prefer longer, more descriptive table names coupled with shorter columns names. Then use aliases when writing queries.&lt;p&gt;&lt;pre&gt;&lt;code&gt;    SELECT * FROM long_table_name l LEFT JOIN another_table_here a ON l.id = a.rel_id
&lt;/code&gt;&lt;/pre&gt;
Also, most tables end up being used to populate objects. It&amp;#x27;s simpler to reference an object with `Reservation.id` than `Reservation.reservation_id`&lt;p&gt;Regardless of the design choices made it&amp;#x27;s more important to be consistent in naming conventions and to document why a particular design&amp;#x2F;schema was chosen.</text></comment>