1

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?

Improve this question
4
  • Create generic table ASSETS which will contain all ITEMS, SKILLS, etc. This way all dependencies can be easily retrived. It comes at a price of more complicated maintenance but i guess you'll hardly insert and update this dependencies every minute.
    – Serg
    May 14, 2016 at 11:18
  • That would fix a large part of the problem, however, how would I go about tackling that different dependencies have different value types? Items are always integers, stats are always numbers between 0 and 100 with 5 decimal places, etc?
    – MrBobJamesBob
    May 14, 2016 at 11:44
  • I see no "ideal" solution. Two options are to have all possible types in one table , e.g for Min attribute you'll have MinInt, MinFloat, MinDate, ... plus convinience views for types, for ITEMS, SKILLS. Or to have extension tables for every type.
    – Serg
    May 14, 2016 at 12:28
  • Could you add some concrete examples? If your image is correct, your 3 action tables can be collapsed into one table (action_id, action_name, req_id, req), or if your keys aren't correct, maybe (action_id, action_name, stat_id, stat_req, skill_id, skill_req, item_id, item_req). For any action_id, you would know the required stat, skill and item and the value; add item_min_req, item_max_req each to get a range, add an actionlevel for different level requirements,... But you have to be able to describe exactly what you want to build the datamodel, so add concrete data with all possible features!
    – Solarflare
    May 14, 2016 at 20:04

1 Answer 1

Reset to default
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy