Better naming strategy?

[happyabc]

Which is the better strategy for field names - to encode the table name into the field names or not? E.g. if f1, and f2 are two possible field names for two tables t1, t2, is t1f1 and t2f2 better or just f1, f2. This point becomes more important if the field names are the same such as 'ID' ( in the latter case one would rely on the . to resolve name collisions )

 

[PruSQLer]

I've never seen table names included in the attribute name and can't see what benefit that practice would have. The distinct tables provide the differentiation. All it would do is require people to perform more keystrokes.

 

[rac2]

I think t1f1 is pretty redundant; I don't do that.

Except with foreign keys. If I have t1.id and t2.id, then I will use t1.t2_id as the name of the foreign key in t1.

Even with keys I like using the table name as part of the column name, t1.t1_id and t2.t2_id. This makes a nicer looking JOIN.

SELECT t2.name
FROM t1
JOIN t2 ON
    t2.t1_id = t1.t1_id

Most things have names and, unlike persons, they tend to have a one-part name which we tend to put in a column named name. This is not going to cause a problem within SQL, because an RDBMS will warn you when you have been ambiguous. The problem comes when you pass the data to an application. I think ADODB will allow you to use a query like this

SELECT t2.name, t1.name
FROM t1
JOIN t2 ON
    t2.t1_id = t1.t1_id

But what happens in the application?

companyName = rs.Fields("name").value
divisionName = rs.Fields("name").value

So you must write

SELECT t2.name AS "company_name", t1.name AS "division_name"
FROM t1
JOIN t2 ON
    t2.t1_id = t1.t1_id

I prefer doing this within the limited context where it is needed rather than using the table name as part of every column name.