Database design for a reccomendation system

Question

I am working on a project which will have a recommendation system and I am in the database design phase, I have a good foundation in programming but databases is a new topic for me, I have designed a database but I don't really know if it's good or not.

enter image description here

but mainly the database should serve the following:

1. store users data (including their preferences and the photos they have liked)
2. store photos data (alongside with it's tags which will be used in the recommendation system)

the recommendation system will recommend content based photos so my plan is:

1. fetch the liked photos of the user
2. fetch the tags of each photo
3. find the top 10 similar photos using cosine similarity for each photo he have liked
4. store them in a new table (haven't been created in the diagram yet)

I want to know if the database that I have designed is good or I should work on it, also is there is a better approach for the recommendation system?

• NoChance: What is "user-preferences" for in the above context? Where does "item_id" come from? What happens when you delete a user? How to ensure that Photo-Tags are not duplicate?

Answer 1

It's a lot better now. Simpler than it was before, which is always good.

I still have some remarks:

• The table named tag is probably meant to attach meaning to a photo through a type and name. That's fine, but why do you refer to rows in that table with item_id? Is a tag an item? And if it is, why not call the table item? I think you intend to say tag_id instead of item_id.

• Do photos need a title when they already have tags? Perhaps they do, but it is something to consider. You don't want repeated content in your database.

• You've got to understand what you've got now, and visually organize your tables accordingly. You've got 3 tables that contain entities: user, tag and photo, and there are 3 tables that serve as a link between these entities: user_preferences to link user and tag, liked_photo to link user and photo, and finally photo_tag to link photo and tag. An argument can be made to rename user_preferences to user_tag and liked_photo to user_photo, to make the naming of tables consistent.

• Now that you know that the tables user_preferences, liked_photo and photo_tag are of the same type you can treat them in the same way. For instance, you have added a column called score to the user_preferences table. I assume this is some kind of weight indicating the strength of the preference. Why then, if a user can also prefer a photo, doesn't that have its own score column? The same is true for the photo_tag table, some tags might better describe what's in a photo than others. No matter what you decide now, it should be a choice for which you have good reasons.

As I said at the beginning, this is a big improvement. Time to fill the tables with data and code the interface. I'm sure you'll still have many hurdles to take, but that's the best way to learn.

• PixD: * At the begging I have named the table items, so I refered to it's rows with item_id and after that I have decided that the tag name will be more convinient, so I renamed it and forgot to rename the refered row, that's a good remark. *Each photo should have a title alongside with it's tags of course yes, it's not duplicated ( I have read more about on database normalization as you have said to me last question ;) ). *I have seen that people sometimes recommend to name the linking table with the name of the tables they are linking, but also other people recommend naming it with what it hold