one table or multiple?
I'm writing an app to store 'rulesets' in an SQL database. At the other end, the app will read the data and export it to specifically formatted text files.
The structure of a ruleset is always the same (uid, regex to match, action). A specific rule however will apply to a certain part of the application (eg. whitelist, blacklist, etc.)
Here's my dilema, do I store all the data in one 'ruleset' table with a field for where it is applied, or do I seperate it into seperate tables for each type of ruleset?
Option 1 - one table
uid | rule_type | match | action
Option 2 - multiple tables
uid | match | action
What are the pros and cons of each? To me, multiple tables (Opt2) is easier to work with in the UI and when writing out to files, but requires multiple structural changes if we want to alter how the rulesets are structured. Option 1 is straightforward but requires more WHERE clauses when using the data.
If it helps on performance grounds, there will be more reads than writes to the data.
I didn't get the best idea of the 2 possible options...both only list 1 possible table. W/out knowing what the table architecture is like, I'll give the following...
For your case (a 1 to many relationship, 1 app can have many rules) I'd create 3 tables...2 for each entity and 1 that relates 'em.
RuleSet (ID, Name, action, etc)
Appication (ID, type, name, etc)
RulesToAps (ApplicationID, RuleID, etc)