SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Zealot
    Join Date
    Sep 2003
    Melbourne, Victoria, Australia
    0 Post(s)
    0 Thread(s)

    Question 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.


  2. #2
    SitePoint Enthusiast
    Join Date
    Apr 2004
    0 Post(s)
    0 Thread(s)
    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)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts