So imagine you've multiple tables inside your database each with it's own structure and every having a PRIMARY KEY of it's own.

Now you need to possess a Faves table to ensure that customers can also add products as faves. Since you will find multiple tables the very first factor that is available in thoughts are to produce one Faves table per table:

If you have a table known as Posts with PRIMARY KEY (publish_id) and also you produce a Publish_Faves with PRIMARY KEY (user_id, publish_id)

This could most likely function as the easiest solution, but can it be easy to have one Faves table joining across multiple tables?

I have regarded the next just as one solution:

Produce a new table known as Master with primary key (master_id). Add triggers on all tables inside your database on place, to develop a new master_id and write it across the row inside your table. Also let us take into account that we write within the Master table, in which the master_id has been utilized (which table)

You can now have one Faves table with PRIMARY KEY (user_id, master_id)

You are able to choose the Faves table and join with every individual table around the master_id and obtain the the faves per table. But will it be easy to get all of the faves with one query (not a question, but a saved procedure?)

Do you consider that this can be a stupid approach? Because you will work one query per table what exactly are you attaining by getting just one table?

What exactly are your ideas around the matter?

One of the ways wold be to sub-type all possible tables to some generic super-type (Entity) and than link user preferences to that particular super-type. For instance:

alt text

I believe you are on course, but a table-based inheritance approach could be great here:

Produce a table master_ids, with only one column: an int-identity primary key area known as master_id.

In your other tables, (users for example), alter the user_id column from becoming an int-identity primary answer to being just an int primary key. Next, make user_id an overseas answer to master_ids.master_id.

This largely preserves data integrity. The only real placed you can trip up is for those who have an expert_id = 1, with a personidentification = 1 along with a publish_id = 1. For any given master_id, you ought to have just one entry across all tables. Within this scenario you've got no method of knowing whether master_id 1 refers back to the user in order to the publish. A method to make certain this does not happen would be to give a second column towards the master_ids table, a type_id column. Type_id 1 can make reference to customers, type_id 2 can make reference to posts, etc.. Then you're virtually good.

Code "gymnastics" might be a little essential for card inserts. If you are utilizing a good ORM, it should not be considered a problem. Otherwise, saved procs for card inserts are what you want. But you are getting your cake and eating it too.

An alternative choice to your approach may be to achieve the faves table as user_id, object_id, object_type. When placing within the faves table just place the kind of the favourite. However dont visit a simple query having the ability to use your approach or mine. One method to do it may be to make use of UNION and obtain one combined resultset after which identify which kind of record it is dependant on the kind. Another factor you should do is, turn the UNION query right into a MySQL VIEW and just query that VIEW.

The advantage of utilizing a single table for faves is really a simplicity, which some might consider compared to the database normalization rules. But around the upside, you do not have to produce a lot of faves table and you will add almost anything to faves easily just by approaching with a brand new object_type identifier.

I am unsure I truly comprehend the alternative you intend.

However in general, when given the option of 1) "more tables" or 2) "a mega-table based on a lot of fancy code work" ..your interests would be best offered by more tables with no code gymnastics.

A Warning Sign was "Add triggers on all tables inside your database" each trigger fire is really a performance hit of it's own.

The database designers have built-in a myriad of technology to optimize tables/indexes, a lot of it behind the curtain without you realizing it. Just relax and relish the ride.

Try these for inspiration Database Answers affiliation in my experience.