[SOLVED] Best practice to name columns for multi-join tables

[mylo]

Hello,

 

i try to model a database having 6 or 7 tables. The lookups will mostly need to join all of them together to get the desired results. It will consist of 1:n, m:n and 1:1 tables.

 

So, after a view tests i ran into trouble using the same column name like "code" or "title" inside the tables, because they get mixed/overlapped by using inner joins. Then is decide to prefix all column names with their table name to get it unique.

 

Is this the way to do it? One main problem is using Perl DBI to access the tables (but other language like PHP may have this problem also) using hashes to retrieve the resulting records. Common column names will overwrite each other in the result, so i can't decide between "tab1.title" and "tab2.title" because it will only contain "title" as a field.

 

So with non-unique column names i need to create an alias for each column which exists in two tables.

 

Well, i'm not an experienced DB-Modeller but did read some papers about it. I can't get to a decition what is why i ask for "best practice" here.

[Barand]

Why are you selecting the column from both tables when you know they'll both have the same value when you join on them?

 

If you aren't joining on them, that's a different problem. I usually do something like

 

SELECT c.name as catname, s.name as subname

FROM category c

JOIN subcategory s ON s.catid = c.id

[mylo]

I do it the same way like you, but name the field containing the foreign key of the id like: {foreign table name}_{foreign key name}.

Example:

table users

id INT

 

table groups

users_id INT  <- this contains the id's of table "users" which i call "foreign key".

 

I used to name tables in plural form.