CODEWITHSUNDEEP
mysqlschema
Paul
Paul

Reputation: 141877

SQL Table Schema question

I recently started appending the name of my table to all my columns in a table. So say I had these tables:

Companies:

Id | Name | Logo | Info


Companies Employees:

Company Id | Employees Id


Employees:

Id | Name

I would change the column names to:

Companies:

Company Id | Company Name | Company Logo | Company Info


Companies Employees:

Company Id | Employee Id


Employees:

Employee Id | Employee Name

Which allows me to use queries like SELECT * FROM `Companies` NATURAL JOIN `Companies Employees` NATURAL JOIN `Employees` without worry about renaming the Id and Name columns since I'm guaranteed there won't be columns with the same name is my Companies and Employees table. But I'm wondering if this is good schema design practice? I don't want to make a habit of it if there are good reasons against it, but I haven't been able to think of any other than a lot of redundant retyping the table name.

Upvotes: 1

Views: 228

Answers (4)

Shef
Shef

Reputation: 45589

Two bad practices in one post.

1. Don't do it like that, it doesn't add neither to functionality, nor to readability. The only place you need to use the table name prefix is on the relationship table.

2. Don't use NATURAL JOIN's

Upvotes: 1

Álvaro González
Álvaro González

Reputation: 146490

Joining tables without explicitly naming key columns works great with simple queries but tends to mess things up when you need to join three or more tables. I've found that problem every time I've tried to replace the ON clause with USING().

E.g.: Let's say you want to obtain a sales report by city. You can consider the office city or the customer city. Both tables customer and office will have a city_id foreign key pointing to the city table but NATURAL JOIN cannot know which one you want to join on as soon as you query customers and offices in the same query.

In any case, using unique columns by itself is a good trick. It saves you from a lot of SELECT foo.id AS foo_id....

Upvotes: 0

Sam Dufel
Sam Dufel

Reputation: 17598

Adding the name of the table to each column can be helpful in a few different situations-

  • When you join 2 tables which would otherwise share several column names, it saves you from having to assign aliases to distinguish columns

  • I tend to use database column names throughout my web applications to maintain consistency; I'll keep the column name in form fields, variables, etc which deal with the same data item. In larger databases with over 100 tables, it helps me stay organized having the table name prefixed on the variable. Not to mention, it can help someone who's new to a project if they can see which table a variable came from.

  • When you're specifying a foreign key in a table, it helps immensely if you have a naming convention which specifies which table the key belongs to. There's nothing worse than seeing a foreign key and not knowing which table it is supposed to belong to.

The only downside to prefixing columns with the table name is that it can get a bit lengthy.

Upvotes: 0

stukselbax
stukselbax

Reputation: 5935

why so hard to write Companies.Name or Employee.Id? I think it is waste of your time.

Upvotes: 0

Related Questions