Field naming convention?

[vitalyb]

When I created the database I first gave the following names:

Table: users
Fields:

• user_id

• user_name

• user_sex

But then I decided that it is silly to write user_ in a users table and changed it to:

Fields:

• id

• name

• sex

Now the problem is that when I do, from PHP a query with a join from users and another table where I use similar convention I actually get two id fields and they overlap each other in the resulting array. The only solution to this is using aliases, which seems rather ugly to me.

So in general the question is what kind of convention you suggest me to use and if it is the second convention, then how would you solve the PHP situation.

Thanks.

[Sxooter]

There's two fairly easy solutions here. One is to use a name for a dependent field like:

parenttab_id

so you'd have:

create table parent (id int primary key, info text);
create table child (parent_id int references parent, moreinfo text);

that way if you:

select * from parent p join child c on (p.id=c.parent_id);

you'll get all the fields with unique names, albeit parent_id and id will be the same number.

the other is to just select the fields you want:

select p.id, p.info, c.moreinfo from parent p join child c on (p.id=c.parent_id);

Now you just get the fields you want.

[vitalyb]

But the child table would have an id field of its own too... Along with the parent_id that is, so the first solution won't work.

As for the second solution it is fine only if I don't need both ids together and I might need them somewhere.

[Sxooter]

Then you can either alias them or give them names like cid:

select p.id, p.info, c.id as child_id, c.moreinfo from parent p join child c on (p.id=c.parent_id);