1

I'm designing a db table that will save a list of user's favorited food items. I created favorite table with the following schema

id, user_id, food_id

user_id and food_id will be foreign key linking to another table.

Im just wondering if this is efficient and scalable cause if user has multiple favorite things then it would need multiple rows of data.

i.e. user has 5 favorited food items, then it will consist of five rows to save the list for that user.

Is this efficient? and scalable? Whats the best way to optimize this schema?

thnx in advance!!!

Improve this question
1
  • 1
    You should accept answers if they were helpful. This help others as well. Commented Nov 28, 2012 at 9:15

5 Answers 5

Reset to default
6

tldr; This is called a "join table" and is the correct and scalable approach to model M-M relationships in a relational database. (Depending upon the constraints used it can also model 1-M/1-1 relationships in a "no NULL FK" schema.)

However, I contend that the id column should be omitted here so that the table is only user_id, food_id. The PK will be (user_id, food_id) in this case.

Unlike other tables, where surrogate (aka auto-increment) PKs are sometimes argued for, a surrogate PK generally only adds clutter in a join table as it has a very natural compound PK.

While the PK itself is compound in this case, each "joined" table only relates back by part of the PK. Depending upon queries performed it might also be beneficial to add covering indices on food_id or (food_id, user_id).

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

2 Comments

Thnx for quick response. On the other hand i was thinking of just making the food_id as array. So each users favorite list will be saved in an array as oppose to adding multiple row. Is this more efficient?
@mamonluk Don't do that: it is a poor design called "denormalized data" - it eliminates the ability of referential integrity constraints and will make many operations slower and/or more difficult. They are called Relational databases for a reason and have been used without such "optimizations" for the last 40+ years. If there is no performance test-case then there is no performance problem.
2

Eliminate Surrogate Key: Unless you have a specific reason for the surrogate key id, exclude it from the table.

Fine-tune Indexing: A this point, you just have a composite primary key that is the combination of the two foreign keys. In which order should the PK fields be?

  • If your application(s) predominantly execute queries such as: "for given user, give me foods", then PK should be {user_id, food_id}.
  • If the predominant query is "for given food, give me users", then the PK should be {food_id, user_id}.
  • If both query "directions" are common, add a UNIQUE INDEX that has the same fields as PK, but in opposite directions. So you'll have PK on {user_id, food_id} and index on {food_id, user_id}.

Note that InnoDB tables are clustered, which eliminates (in this case "unnecessary") table heap. Yet, the secondary index discussed above will not cause a double-lookup (since it fully covers the query), nor have a hidden overhead of PK fields (since it indexes the same fields as PK, just in opposite order).

For more on designing a junction table, take a look at this post.

Improve this answer

4 Comments

Hi, what if I make food_id as array, then I can just store users favorite list in a single row. Is this efficient? thnx
@mamonluk You'd be violating the principle of atomicity and therefore the 1NF, and there are very good reasons not to do that, most importantly referential integrity (i.e. FOREIGN KEYs). And if your DBMS doesn't support arrays natively (I think MySQL doesn't), the database won't be able to enforce the integrity of domain (i.e. "type safety") neither. If you need to query in only one direction (and modify rarely), you might actually get away with it performance-wise, but you lose the flexibility if you ever need to go in the opposite direction.
thnx. Nicely explained. I have a totally diff question. I'm not sure if it's okay to ask here. What if i want to design db that saves a user's transaction. i.e. user bought list of items. How would i store them. I'm assuming I would have table called transaction that saves each user's item with the transaction id?
@mamonluk The problems is that prices (and possibly other information, such as delivery address) should be memorized as they were at the time of the purchase, even if they change later. There are couple of ways how this can be handled, for some ideas look here and here.
1

To my opinion, you can optimize your table in the following ways:

  1. As a relation table with 2 foreighkeys you don't have to use "id" field.
  2. use "innodb" engine to your table
  3. name your relation table "user_2_food", which will make it more clear.
  4. try to use datatype as small as possible, i.e. "smallint" is better than "int", and don't forget "UNSIGNED" attribute.
Improve this answer

Comments

0

Creating the below three Tables will result in an efficient design.

users : userId, username, userdesc
foods : foodId, foodname, fooddesc
userfoodmapping : ufid, userid, foodid, rowstate

The significance of rowstate is, if the user in future doesn't like that food, its state will become -1

Improve this answer

1 Comment

I would avoid using ufid in userfoodmapping table unless this table has sufficently large no of columns and this table is being referenced in many tables which is not the usual case.
0

You have 2 options in my opnion:

  1. Get rid of the ID field, but in that case, make both your other keys (combined) your primary key

  2. Keep your ID key as the primary key for your table.

In either case, I think this is a proper approach. Once you get into a problem of inefficiency, then you will look at probably how to load part of the table or any other technique. This would do for now.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.