mysql - How do you avoid column name conflicts? -

i assigned task of creating auction system. during work, met numerous occasions sql queries contained joins failed execute due ambiguous column names. consider (simplified) table structure auction:

table auction:

• id
• name
• uid (id of user created auction)

table item:

• id
• name
• uid (id of user added item)
• aid (id of auction item available)
• price (initial price)

table user:

• id
• name

table bid:

• id
• uid (id of user placed bid)
• iid (item price has been raised)
• price (offered price)

as can see, there numerous columns have conflicting names. joining these tables requires using measures clear ambiguities.

i can think of 2 ways this. first rename columns, prefixing of them abbreviated table name, auction id become a_id, item id become i_id, , item id within bid table become b_i_id. pretty solid, reduces readability of column names.

another way can think of writing explicit queries:

select `bid`.`id`, `user`.`name`, `bid`.`price` `bid` join `item` on `item`.`id` = `bid`.`iid` join `user` on `user`.`id` = `bid`.`uid` join `auction` on `auction`.`id` = `item`.`aid` `bid`.`price` > `item`.`price` , `auction`.`id` = 1 group `user`.`id` order `bid`.`price` desc; 

this readable , unambiguous, requires lots of keystrokes.

i use second approach, maybe there others have successfuly used in similar situations? how avoid column name conflicts in sql queries?

my experience that benefits of extra keystrokes outweighs short time takes type them by far in long run, @ latest when need @ query have written year ago or so.