MySQL database design for easy item relationships and meta storage

Question

I'm playing around with database designs to use for a new system. This system at its core revolves around various types of items (users, accounts, images), their meta (names, titles, file sizes), and item's relation to each other (users mapped to accounts, images mapped to users).

Comments

1. I created the uid solution so I could store all item meta in the same table, rather than having a unique table for each item type with its own custom columns. This way I have one item table, and one meta.

Questions

1. Since tbl_item doesn't have any columns besides type, things like user passwords and usernames/emails would be stored in tbl_meta. I have never seen a database where passwords (hashed of course) and usernames were not stored in their own table. Is there a reason they shouldn't be tbl_meta?
2. I'm not a query expert, and I wonder if either of my queries below have side effects or are written poorly. Thoughts/suggestions?
3. General comments/suggestions on the database as a whole. I'm fairly new to database design so any instruction will be helpful.

Database Scheme

I have three tables: tbl_item, tbl_meta, and tbl_map. Below is the scheme for each table.

CREATE TABLE IF NOT EXISTS `tbl_item` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `type` varchar(50) NOT NULL,
 `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `tbl_map` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `p_id` int(11) NOT NULL,
 `c_id` int(11) NOT NULL,
 `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 KEY `FK__tbl_item` (`p_id`),
 KEY `FK__tbl_item_2` (`c_id`),
 CONSTRAINT `FK__tbl_item` FOREIGN KEY (`p_id`) REFERENCES `tbl_item` (`id`),
 CONSTRAINT `FK__tbl_item_2` FOREIGN KEY (`c_id`) REFERENCES `tbl_item` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `tbl_meta` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `item_id` int(11) NOT NULL,
 `key` varchar(255) NOT NULL,
 `value` varchar(255) NOT NULL,
 `uid` int(11) NOT NULL,
 `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 UNIQUE KEY `item_id_key` (`item_id`,`key`),
 UNIQUE KEY `value_uid` (`value`,`uid`),
 CONSTRAINT `FK_tbl_meta_tbl_item` FOREIGN KEY (`item_id`) REFERENCES `tbl_item` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_item and tbl_map are pretty straight forward. An item has a unique id and a type. and tbl_map creates parent child relations. tbl_meta contains key/value pairs that assigned to an item.

Queries

The column uid (unique id) is what makes this table useful. The unique constraint on value and uid is so that I can set a key to have unique values across all items. I have two queries for for updating this table.

1. This one is used when inserting meta that is not unique:

   INSERT INTO tbl_meta
   (`item_id`, `key`, `value`, `uid`)
   VALUES
   (1, 'my_key', 'my_value', 
   (SELECT (`uid` + 1) as uid FROM `tbl_meta` as `tbl_meta2` ORDER BY `id` DESC LIMIT 1)
   )
   ON DUPLICATE KEY UPDATE
   `value`=VALUES(value)
   

2. This one is used for inserting meta that needs to be unique across all items. It insures that that key has the same uid, and the unique constraint on value and uid makes sure that no duplicate values are entered.

   INSERT INTO tbl_meta
   (`item_id`, `key`, `value`, `uid`)
   VALUES
   (1, 'my_key', 'my_value', 
    (SELECT 
    IFNULL (
    (
    SELECT uid FROM `tbl_meta` as `tbl_meta2` 
    WHERE `key`='my_key' 
    ORDER BY uid DESC LIMIT 1
    ),
    (SELECT (`uid` + 1) as uid FROM `tbl_meta` as `tbl_meta2` ORDER BY `id` DESC LIMIT 1)
    ) as uid)
   )
   ON DUPLICATE KEY UPDATE
    `value`=VALUES(value)
   

Examples

For these examples, assume an account already exists in the database.

1. Create a new user and add meta to that user.

   A user is a type of item. So insert a row into tbl_item with type='user' then insert the users password, email, and name into tbl_meta. password, and name are not unique so those values would be inserted using query 1 above. Email needs to be unique across all users (because that's what they log in with), so email would be inserted using the second query. That query will make sure that no item has the same value for the key email.

2. Add a profile photo to a user.

   An image is a type of item. So insert a row into tbl_item with type='image' then insert the images title and url into tbl_meta. Title and url are not unique so use query 1. Then add the meta profile_photo='img_id' where img_id is the id of the image we just inserted into the database.

3. Map the user to an account

   An account is a type of item. An account can have multiple users mapped to it. To map our new user to an account, insert a row into tbl_map where p_id (parent item's id) is the account's ID and c_id (child item's id) is the id of the user.

Result

We started with an account. Now we have a user mapped to that account with meta (password, email, name, profile_photo). As well as an image and its title and URL.

• 200_success: These names look strangely generic: 'my_key', tbl_meta, tbl_item, etc. Is this really what appears in your code, or did you "sanitize" it before posting?
• Craig Harshbarger: @200_success Its a mysql database that could be used for many different systems. Its meant to be flexible and easy to use. The table names and data in the code are sample values, so you could say they are sanitized. I didn't think the database names and data were important to the question.
• Craig Harshbarger: Let us continue this discussion in chat.

Answer 1

---

1. Location for user passwords and others

Obviously I'm not familiar enough with your current project to be assertive, but at a first view I'm not convinced there is an advantage of locating all these data in tbl_meta.

Maybe you consider it as a good simplification, something like "Why bother with an additional table, while I defined a perfect way to save any kind of data?".
Yes, your tbl_meta-related mechanism is a good tool to face many different needs whithout having to create many different additional structures. But that's true just for many different things, not for the project main data.

Especially when there are a number of fields for the same table: in the case of users, you'll end with a number of tbl_meta records (one for each field-equivalent) in place of one tbl_user record.
At the same time, it'll much increase the engine workload.
Last but not least, it makes the entire database less directly readable: what about the equivalent of a simple display of tbl_user through PhpMyAdmin?

So actually I'd not recommend that.

---

2. Queries

As a simple mention, you can suppress all occurrences of "as tbl_meta2", which are not used.

In the other hand, I couldn't understand the way you get uid values.

At this time, your question was just put on hold, so I can't say more for now.

---

3. General comments/suggestions

Readability

I'd highly recommend, first to choose self-expanatory field names where possible, then using COMMENTs in table definition both at table and column levels.
It's especially useful in the current case, where your opted for a not obvious organization.

E. g.: you must realize that first reading table schemes, one can't at all figure out what are the p_id and c_id fields of tbl_map.
And looking at the CONSTRAINTs is of no help, since these two fields reference the same foreign field!
In this example, you might merely choose to name your fields parent_id and chield_id without it weighs further coding.

Consistency

In the tbl_meta, the FK_tbl_meta_tbl_item constraint doesn't conform to the same naming convention than those stated for tbl_item (or vice-versa, as you want): the former looks like FK__(current-table)_(referenced-table) while it's only FK__(referenced-table) for the latter.

• Craig Harshbarger: Thank you for your feedback. Still reviewing your comments. In answer to how I get uid values. It selects the uid of the last row with the same key value. If there is no row with the same key value, it selects the last inserted uid and increments it.
• Craig Harshbarger: I do understand that inserting a new user will have more of a tax on the database than inserting a single row into a users table. But the majoring of database interaction is adding, and updating values associated with an item. Which this database makes simple, no matter what type of item is being udpated.
• cFreed: @CraigHarshbarger Regarding the option to not use a dedicated users table, sure it depends on numerous factors to weigh the pros and cons. But here is again a con: if the number of users is not negligable, their simple presence in tbl_meta will impact performance for the rest of the data processing. In the other hand, reagarding uid, what I espcially don't understand is that (in the common query1/2 part) you seem to select the less found value, so I can't realize its role and utility. BTW I don't understand why your question was put on hold: I found it quite acceptable.
• Craig Harshbarger: what I'm understanding is that storing mass amounts of data in a single table will impact performance for the individual items. And that separating things into different tables keeps data processing to a minimum. In reguards to UID. If the value does not need to be unique, it gets its own uid (the last used uid + 1). If it does need to be unique, the uid from the last inserted row with the same key is used. If no uid is found, a new uid is inserted (last uid + 1). Hope that makes sense.