CODEWITHSUNDEEP
phpmysql
Chris Farrugia
Chris Farrugia

Reputation: 1046

How should I name fields in a relational database?

I'm about to create a database that has fields that will appear in a few different tables (as keys). For example, in my listing table I will have MLS_ID. This field will also appear in the listing_photos table. Should I prefix the field name to make it unique: listings_MLS_ID as an example?

Upvotes: 0

Views: 208

Answers (3)

Marcus Adams
Marcus Adams

Reputation: 53830

Name the key the same in both tables (MLS_ID). The database server rewards you for doing this by letting you use the more concise USING clause:

SELECT * FROM listings
JOIN listing_photos
  USING (MLS_ID)

Rather than this:

SELECT * FROM listings
JOIN listing_photos
  ON listing_photos.listings_MLS_ID = listings.MLS_ID

Upvotes: 1

Randy
Randy

Reputation: 16677

this is personal preference.

for me:

i do not prefix with the local table name except on the primary key. e.g. PERSON table will get a PERSON_ID column, ADDRESS table will get ADDRESS_ID etc.

the PERSON_ADDRESS table gets PERSON_ID and ADDRESS_ID, not something else.

in your queries, the column names are equal when they should be equivalent as keys, and the table alias tells you which is which.

Upvotes: 0

Your Common Sense
Your Common Sense

Reputation: 157838

there is absolutely no point in doing that. you could ALWAYS prefix your fieldname with both database and table names, getting your unique identifier, keeping the name itself neat and concise.

listings.mls.id

will address an id field in the mls table which belongs to listings database.

this is going to be quite flexible: any time you need long unique identifier - you have it.
in all other cases you can use short name.

Upvotes: 2

Related Questions