Mysql – How to handle id’s referring to multiple tables in an SQL database

MySQLtable

I'm working on an MySQL Database for my website but I'm not sure on how to proceed now. I get to the point where actions have requirements in multiple other tables, but also have influences in multiple other tables.

this

In my end design I will have at least 6 tables that an action has potential requirements for and a potential influence on.

Now, there are 2 ways I've thought of it myself:

1 Make action_req_x and action_inf_x tables for every x which would look like this:

action_id ...
x_id ...
minvalue ...
maxvalue ...

Where the minvalue and maxvalue can be changed upon which table it's refferencing. For example, stats would be Decimal(10,3) and items would be Integer(1). This method would require a LOT of tables and I can't believe I've found the best method on my own.

2 Make action_req_x and action_inf_xtables, but only one of each, which would look like this:

action_id ...
x_tablename ...
x_id ...
minvalue ...
maxvalue ...

but this wouldn't be perfect, because different tables have different types of values and I wouldn't know how to setup FK's 🙁 I don't necessarily think this is a really good way to go, but it's better than nothing..

Now, so far I've only talked about the influences and requirements for actions, now I'm not sure yet, but I might eventually want items to have requirements and influences as well, which would require even MOOOORE tables!

My question: Is there a better way to handle this than my first method? Or is there a way I can alter my second method and make that work better? How would database professionals attack this problem?

Best Answer

Don't "over-normalize".

The table stats, items, and skills are just normalizations of short, unique, names. Consider just having the names in the other tables.

Or... have just one table for id<->name mapping, perhaps with an ENUM to say which of stat/item/skill/etc it is.

Based on your Schema, what queries will you be performing? Do you ever need to 'start' with action_influence? Or is it just some extra info to enter and later display? If the latter, consider putting that into a JSON string of all the reqs, influences, etc that are associated with an action. Then have that JSON string in the actions table. If you use MySQL 5.7 or MariaDB 10, there are even tools for having SQL reach into the JSON, but try to avoid that.

Related Question