more efficient database structure across multiple tables

Question

I am setting up a MySQL database with multiple tables. Several of the tables will have fields with similar names that aren't necessarily for the same purpose.

For example, there's a users table that will have a name field, a category table with a name field and so on.

I've previously seen this setup up either with or without a preface to the field name, so in the above example using user_name, cat_name etc.

As these are all in separate tables, is there any benefit to structuring the database with or without this preface? I know that when using joins and calling the data through PHP you have to add a SELECT users.name AS username... to keep the fields from overwriting each other when using mysql_fetch_array. But i'm not sure if there's any efficiencies in using one method over the other?

If you were using a DBMS that supported "create domain", the kind of data management issue you are raising would best be addressed by whether user names and category names should be based on the same domain. And the answer depends on whether or not a user with name "XXX" and a category with name "XXX" are somehow referring to the same thing. The issue here is semantics, not format. — Walter Mitty
– Walter Mitty, Commented Apr 6, 2012 at 20:10

JonH · Accepted Answer · 2012-04-06 19:27:28Z

1

It depends on what your shop does or your preference. There is nothing about a prefix that will make this better. Personally I would just keep it as name since: Users.Name and Orders.Name and Products.Name all contain tuples with different object types.

At the end of the day you want to be consistent. If you prefer a cat_ and a user_ prefix just be consistent with your design and include this prefix for all object types. To me less is more.

answered Apr 6, 2012 at 19:27

JonH

33.2k13 gold badges93 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mfonda · Accepted Answer · 2012-04-06 19:27:31Z

0

It's really just a matter of preference. I personally prefer the approach of using just name.

One thing to watch out for though, if you're doing any SELECT * FROM ... queries (which you shouldn't be; always select fields explicitly), you may end up selecting the wrong data.

answered Apr 6, 2012 at 19:27

mfonda

8,0431 gold badge29 silver badges30 bronze badges

2 Comments

JonH Over a year ago

You don't get wrong data, you'll get an error that the name exists in two or more different tables, this is why you should alias and explicitly define each field you want returned.

TH1981 Over a year ago

yeah, i ran across this when I first started creating databases. It didn't give any errors, just when I combined tables the resulting array would overwrite the $query['name'] value with each instance of that variable name rather than distinguishing.

HLGEM · Accepted Answer · 2012-04-06 19:30:04Z

0

One disadvantage is if anyone is stupid enough to use natural joins (you can guess that I find this a poor practice but mysql does allow it so you need to consider if that will happen) you may end up joining on those fields with the same name by accident.

answered Apr 6, 2012 at 19:30

HLGEM

97k15 gold badges120 silver badges191 bronze badges

1 Comment

gahooa Over a year ago

I agree on the bad practice (in a query that will be run over time). Natural joins and SELECT * are non-determinstic in the face of adding columns to a table. Baaaaad.

Collectives™ on Stack Overflow

more efficient database structure across multiple tables

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related