Whether to merge avatar and profile tables?

I have two tables:

Avatars:
Id     | UserId    | Name            | Size
-----------------------------------------------
1      | 2         | 124.png         | Large
2      | 2         | 124_thumb.png   | Thumb

Profiles:
Id     | UserId    | Location    | Website
-----------------------------------------------
1      | 2         | Dallas, Tx  | www.example.com

These tables could be merged into something like:

User Meta:
Id     | UserId    | MetaKey        | MetaValue
-----------------------------------------------
1      | 2         | location       | Dallas, Tx
2      | 2         | website        | www.example.com
3      | 2         | avatar_lrg     | 124.png
4      | 2         | avatar_thmb    | 124_thumb.png

This to me could be a cleaner, more flexible setup (at least at first glance). For instance, if I need to allow a "user status message", I can do so without touching the database.

However, the user's avatars will be pulled far more than their profile information.

So I guess my real questions are:

What king of performance hit would this produce?

Is merging thes开发者_StackOverflow中文版e tables just a really bad idea?

This is almost always a bad idea. What you are doing is a form of the Entity Attribute Value model. This model is sometimes necessary when a system needs a flexible attribute system to allow the addition of attributes (and values) in production.

This type of model is essentially built on metadata in lieu of real relational data. This can lead to referential integrity issues, orphan data, and poor performance (depending on the amount of data in question).

As a general matter, if your attributes are known up front, you want to define them as real data (i.e. actual columns with actual types) as opposed to string-based metadata.

In this case, it looks like users may have one large avatar and one small avatar, so why not make those columns on the user table?

We have a similar type of table at work that probably started with good intentions, but is now quite the headache to deal with. This is because it now has 100s of different "MetaKeys", and there is no good documentation about what is allowed and what each does. You basically have to look at how each is used in the code and figure it out from there. Thus, figure out how you will document this for future developers before you go down that route.

Also, to retrieve all the information about each user it is no longer a 1-row query, but an n-row query (where n is the number of fields on the user). Also, once you have that data, you have to post-process each of those based on your meta-key to get the details about your user (which usually turns out to be more of a development effort because you have to do a bunch of String comparisons). Next, many databases only allow a certain number of rows to be returned from a query, and thus the number of users you can retrieve at once is divided by n. Last, ordering users based on information stored this way will be much more complicated and expensive.

In general, I would say that you should make any fields that have specialized functionality or require ordering to be columns in your table. Since they will require a development effort anyway, you might as well add them as an extra column when you implement them. I would say your avatar pics fall into this category, because you'll probably have one of each, and will always want to display the large one in certain places and the small one in others. However, if you wanted to allow users to make their own fields, this would be a good way to do this, though I would make it another table that can be joined to from the user table. Below are the tables I'd suggest. I assume that "Status" and "Favorite Color" are custom fields entered by user 2:

User:
| Id    | Name      |Location    | Website          | avatarLarge | avatarSmall
----------------------------------------------------------------------
| 2     | iPityDaFu |Dallas, Tx  | www.example.com  | 124.png     | 124_thumb.png


UserMeta:
Id     | UserId    | MetaKey        | MetaValue
-----------------------------------------------
1      | 2         | Status         | Hungry
2      | 2         | Favorite Color | Blue

I'd stick with the original layout. Here are the downsides of replacing your existing table structure with a big table of key-value pairs that jump out at me:

Inefficient storage - since the data stored in the metavalue column is mixed, the column must be declared with the worst-case data type, even if all you would need to hold is a boolean for some keys.

Inefficient searching - should you ever need to do a lookup from the value in the future, the mishmash of data will make indexing a nightmare. Inefficient reading - reading a single user record now means doing an index scan for multiple rows, instead of pulling a single row.

Inefficient writing - writing out a single user record is now a multi-row process.

Contention - having mixed your user data and avatar data together, you've forced threads that only one care about one or the other to operate on the same table, increasing your risk of running into locking problems.

Lack of enforcement - your data constraints have now moved into the business layer. The database can no longer ensure that all users have all the attributes they should, or that those attributes are of the right type, etc.