List:General Discussion« Previous MessageNext Message »
From:SGreen Date:October 4 2005 5:09pm
Subject:Re: Joins Tutorial Anywhere?
View as plain text  
My favorite SQL tutorial:

Do your field names need to be globally unique: yes and no.  Some design 
philosophies require that every field on every table be uniquely 
identified. The only uniqueness requirement you must obey is that no two 
fields on the same table can have the same name. What you call your fields 
should reflect the data that's in them and not be too much of a hassle to 
type (not strangely punctuated)

For me, I personally do not use tablename_fieldname to name my fields. 
This disagrees with several style doctrines but it works for me. 

When you join two tables that share field names, both fields will appear 
in the result (named identically)., Your naming pattern would avoid that 
except in the case of self-joins (one table that joins to itself). The 
other way to avoid duplicate field names in your output is to use the 
"alias" feature of SQL.

Let's say you have a table of employees. Each employee gets an ID value, 
has a name, a department, and a boss (identified by it's id value). One 
way to buld this table would look like

CREATE TABLE employee {
        id int auto_increment
        , boss_employee_id int
        , department varchar(16)
        , lastname varchar(32)
        , firstname varchar(32)
        , PRIMARY KEY(id)
        , UNIQUE(firstname, lastname)
(using my preferred naming pattern)

I used "boss_employee_id" instead of "boss_id" because I wanted to 
indicate both what the field contained and where the data came from. Most 
of my fields with _ in their names point to data in other tables, some 
just looked better with the spacer.

If you have tried the tutorial and are still lost, let us know and someone 
will happily work with you.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Rich <lists@stripped> wrote on 10/04/2005 12:43:17 PM:

> Hi folks.  Any chance on a tutorial from joins?  I find them totally
> confusing and I know there's some power in them, so I need to learn 
> Having asked that, another question my field names in 
> tables within the same database have to overlap (same field name) in 
> for joins to work?  I'm currently naming fields in such a manner:
> table1_alpha
> table1_bravo
> table2_firstname
> table2_lastname
> Appreciate any guidance on these two questions.
> Cheers
> -- 
> MySQL General Mailing List
> For list archives:
> To unsubscribe:

Joins Tutorial Anywhere?Rich4 Oct
  • Re: Joins Tutorial Anywhere?Martijn Tonies4 Oct
  • Re: Joins Tutorial Anywhere?SGreen4 Oct
    • Re: Joins Tutorial Anywhere?Rich4 Oct
  • Re: Joins Tutorial Anywhere?Peter Brawley4 Oct
  • Re: Joins Tutorial Anywhere?Ligaya Turmelle5 Oct
    • Re: Joins Tutorial Anywhere?Jason Martin5 Oct