Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Aug 2004
    Location
    The US of A
    Posts
    767
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Database Layout for my Project

    I'm working on developing the database backend and I was wondering how well I have the database laid out so far.

    Here's a visual representation: Kind of Big, so here's a link

    Code:
    CREATE TABLE config (
           config_name VARCHAR(255) NOT NULL,
           config_value VARCHAR(255) NOT NULL
           );
    
    CREATE TABLE bbcode (
           id SERIAL NOT NULL PRIMARY KEY,
           bbcode_pattern VARCHAR(255) NOT NULL,
           replace_pattern VARCHAR(255) NOT NULL,
           name VARCHAR(20) NOT NULL,
           description VARCHAR(255)
           );
    
    CREATE TABLE styles (
           id SERIAL NOT NULL PRIMARY KEY,
           name VARCHAR(50) NOT NULL,
           title VARCHAR(20) NOT NULL,
           herf VARCHAR(50) NOT NULL,
           media VARCHAR(200) NOT NULL,
           alt INTEGER NOT NULL DEFAULT(1)
           );
    
    CREATE TABLE users (
           id SERIAL NOT NULL PRIMARY KEY,
           user_level NUMERIC(2,0) NOT NULL DEFAULT(0),
           name VARCHAR(100) NOT NULL UNIQUE,
           screen VARCHAR(50) NOT NULL UNIQUE,
           password CHAR(32) NOT NULL UNIQUE,
           email VARCHAR(200) NOT NULL UNIQUE,
           show_email INTEGER DEFAULT(1),
           aim VARCHAR(255),
           msn VARCHAR(255),
           icq VARCHAR(255),
           joined NUMERIC(32,0) NOT NULL,
           rank VARCHAR(255) DEFAULT('Dice Roller'),
           avatar VARCHAR(100),
           dateform VARCHAR(14) DEFAULT('d M Y H:i'),
           timezone NUMERIC(5,0) DEFAULT(0),
           style INTEGER DEFAULT(1) REFERENCES styles (id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
           signature VARCHAR(500),
           show_signature INTEGER DEFAULT(1),
           actkey VARCHAR(32) UNIQUE
           );
    
    CREATE TABLE sessions (
           id VARCHAR(32) NOT NULL DEFAULT(0) PRIMARY KEY,
           user_id INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE,
           last_page VARCHAR(200) NOT NULL,
           session_start NUMERIC(11,0) NOT NULL,
           last_active NUMERIC(11,0) NOT NULL,
           last_sid_change NUMERIC(11,0) NOT NULL,
           session_ip VARCHAR(40) NOT NULL,
           session_agent VARCHAR(150) NOT NULL,
           data TEXT NOT NULL
    );
    
    CREATE TABLE races (
           id SERIAL NOT NULL PRIMARY KEY,
           name VARCHAR(255) NOT NULL
           );
    
    CREATE TABLE alignment (
           id SERIAL NOT NULL PRIMARY KEY,
           alignment VARCHAR(255) NOT NULL
           );
    
    CREATE TABLE characters (
           id SERIAL NOT NULL PRIMARY KEY,
           user_id INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE,
           level INTEGER NOT NULL DEFAULT (1),
           name VARCHAR(50) NOT NULL DEFAULT('Ted Bunny Buns'),
           race INTEGER NOT NULL DEFAULT(1) REFERENCES races (id) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT,
           align NUMERIC(1,0) DEFAULT(1) REFERENCES alignment (id) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT,
           hp INTEGER NOT NULL DEFAULT(1),
           ac INTEGER NOT NULL DEFAULT(10),
           money INTEGER NOT NULL DEFAULT (0)
           );
    
    CREATE TABLE stats (
           id SERIAL NOT NULL PRIMARY KEY,
           stat VARCHAR(10) NOT NULL
           );
    
    CREATE TABLE char_stats (
           char_id INTEGER NOT NULL REFERENCES characters (id) ON DELETE CASCADE ON UPDATE CASCADE,
           stat_id INTEGER NOT NULL REFERENCES stats (id) ON DELETE CASCADE ON UPDATE CASCADE,
           stat_level INTEGER NOT NULL,
           modifer INTEGER NOT NULL
           );
    
    CREATE TABLE classes (
           id SERIAL NOT NULL PRIMARY KEY,
           class VARCHAR(100) NOT NULL
           );
    
    CREATE TABLE char_classes (
    char_id INTEGER NOT NULL REFERENCES characters (id) ON DELETE CASCADE ON UPDATE CASCADE,
    class_id INTEGER NOT NULL REFERENCES classes (id) ON DELETE CASCADE ON UPDATE CASCADE,
    level INTEGER NOT NULL DEFAULT(1)
            );
    
    CREATE TABLE skills (
    id SERIAL NOT NULL PRIMARY KEY,
    skill VARCHAR(50) NOT NULL
     );
    
    CREATE TABLE char_skills (
           char_id INTEGER NOT NULL REFERENCES characters (id) ON DELETE CASCADE ON UPDATE CASCADE,
           skill_id INTEGER NOT NULL REFERENCES skills (id) ON DELETE CASCADE ON UPDATE CASCADE,
           stat_id INTEGER NOT NULL REFERENCES stats (id) ON DELETE CASCADE ON UPDATE CASCADE,
           ranks INTEGER NOT NULL DEFAULT(0),
           misc_mod INTEGER NOT NULL DEFAULT(0)
           );
    
    CREATE TABLE weapons (
           id SERIAL NOT NULL PRIMARY KEY,
           name VARCHAR(255) NOT NULL,
           sml_dmg VARCHAR(10) DEFAULT('1d4'),
           med_dmg VARCHAR(10) DEFAULT('1d4'),
           lg_dmg VARCHAR(10) DEFAULT('1d4'),
           critical VARCHAR(10) NOT NULL DEFAULT('x2'),
           effects TEXT
           );
    
    CREATE TABLE char_weps (
           wep_id INTEGER NOT NULL REFERENCES weapons (id) ON DELETE CASCADE ON UPDATE CASCADE,
           char_id INTEGER NOT NULL REFERENCES characters (id) ON DELETE CASCADE ON UPDATE CASCADE,
           amount INTEGER NOT NULL DEFAULT(1)
           );
    
    CREATE TABLE topics (
           id SERIAL NOT NULL PRIMARY KEY,
           user_id INTEGER NOT NULL DEFAULT(-1) REFERENCES users (id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
           topic VARCHAR(200) NOT NULL,
           weight NUMERIC(4,0) NOT NULL DEFAULT(1),
           posted INTEGER NOT NULL DEFAULT(0)
           );
    
    CREATE TABLE comments (
           id SERIAL NOT NULL PRIMARY KEY,
           topic_id INTEGER NOT NULL REFERENCES topics (id) ON DELETE CASCADE ON UPDATE CASCADE,
           user_id INTEGER NOT NULL DEFAULT(-1) REFERENCES users (id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
           post TEXT NOT NULL,
           show_sig INTEGER NOT NULL DEFAULT(1),
           bb_code INTEGER NOT NULL DEFAULT(1)
           );
    
    CREATE TABLE categories (
           id SERIAL NOT NULL PRIMARY KEY,
           name VARCHAR(100) NOT NULL
           );
    
    CREATE TABLE top_cats (
           cate_id INTEGER NOT NULL REFERENCES categories (id) ON DELETE CASCADE ON UPDATE CASCADE,
           topic_id INTEGER NOT NULL REFERENCES topics (id) ON DELETE CASCADE ON UPDATE CASCADE
           );
    The config and bbcode tables kind of hang out on their own since bbcode isn't related to anything else in the database and the config table isn't related to anything in general, though it will control most everything.

    Constructive criticism welcome.

    I'm using Postgres, as usual, if that makes a difference to anyone.

  • #2
    Senior Coder
    Join Date
    Aug 2003
    Location
    One step ahead of you.
    Posts
    2,815
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Won't a `category_id` field (in the `topics` table) be enough? Havong another table would make sense only if a topic could be assigned to many categories.
    I'm not sure if this was any help, but I hope it didn't make you stupider.

    Experience is something you get just after you really need it.
    PHP Installation Guide Feedback welcome.

  • #3
    Regular Coder
    Join Date
    Aug 2004
    Location
    The US of A
    Posts
    767
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Depending on the type of topic, either strictly a news post or a quest day report, it will be posted to posted to mutiple categories. Also, this way I can unify the comments tables for both quest days and news posts as well.

    But I do see your point; I might change that before the site goes out of development.

  • #4
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    There's nothing glaringly awful with that, can I ask what you used to create that diagram?

  • #5
    Regular Coder
    Join Date
    Aug 2004
    Location
    The US of A
    Posts
    767
    Thanks
    1
    Thanked 0 Times in 0 Posts
    phpPgAdmin + Print Screen + Photoshop.

    And also, the more I think about it, the more Marek's suggestion makes sense, I could probably just cut the categories table while I'm at it too, and just use a `type` field to select if it is either a quest log or news post (or a hijink report if I decide to add those again)

    I'm trying to decide how I should go about adding comments for characters too without adding in another table though.

  • #6
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    Oh, I thought it was some cool relation-modelling tool :|


  •  

    Posting Permissions

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