SitePoint Sponsor

User Tag List

Results 1 to 22 of 22
  1. #1
    If it aint Dutch it aint much Kilroy's Avatar
    Join Date
    Oct 2003
    Location
    The Netherlands
    Posts
    406
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Red face Comments on database design

    Hi there,

    I'm currently in the planning stage of building a CMS. I just made a database scheme, and I'm wondering how to improve it. I thought maybe you guys could give me some tips, as to what would be a better structure etc. Here's the scheme (simply laid out, sorry ):

    users
    uid -> mediumint, primary key, auto_increment, unsigned
    username -> varchar (50)
    first_name -> text
    last_name -> text
    country -> text
    age -> tinyint, unsigned
    password -> md5
    activated -> enum ("Yes", "No")
    signup_date -> date
    avatar -> text [this will be a link]
    signature -> text
    user_level -> mediumint

    userlevels
    user_level -> mediumint, primary key, auto_increment, unsigned
    title -> text
    description -> text
    state -> enum ("live", "development")

    content
    id -> mediumint, primary key, auto_increment, unsigned
    uid -> mediumint, unsigned [refers to the user who made the content]
    cid -> mediumint, unsigned
    title -> text
    header -> text
    date -> date
    meta_info -> text
    content -> text
    state -> enum ("live", "development")

    categories
    cid -> mediumint, primary, auto_increment, unsigned
    pid -> mediumint, unsigned [parent-id]
    title -> text
    description -> text
    state -> enum ("live", "development")

    menu
    id -> mediumint, primary key, auto_increment, unsigned
    name -> varchar (100)
    url -> text
    pid -> mediumint, unsigned [parent-id]
    state -> enum ("live", "development")

    sessions
    username -> varchar (50)
    uid -> mediumint, unsigned [refers to the user who made the content]
    time -> varchar (14)
    session_id -> varchar (200) NOT NULL
    guest -> tinyint (4)


    Could you guys please have a look at this and tell me what you would do differently, and why?

    I am particularly wondering about things like should I make id's int's, mediumint's, or something else?

    Greetz,

    Peter

  2. #2
    Non-Member
    Join Date
    Jan 2004
    Location
    Planet Earth
    Posts
    1,764
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not really the forum for this sort of thing

    Can an administrator move this to the Database forum, please ?

    Though, quickly

    Code:
    ...
    first_name -> text
    last_name -> text
    country -> text
    ...
    I would make these VARCHAR( <value> ) as TEXT has a length of 65535 characters, which is way too much

    Also, I would make a Username UNIQUE, likewise with Password, though this is optional - so long as at least one, Username and/or Password is UNIQUE.

    You may also want to think about an Email as well ? This to, would be UNIQUE

  3. #3
    Non-Member
    Join Date
    Jan 2004
    Location
    Planet Earth
    Posts
    1,764
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    userlevels
    user_level -> mediumint, primary key, auto_increment, unsigned
    title -> text
    description -> text
    state -> enum ("live", "development")
    This may be adequate just now, though if your CMS grows later, you'll have big problems refactoring user access, so you may want to look into Roles and Permissions based authentication instead ?

    Belief me, I used an INTEGER based access level to begin with, but quickly found out how limiting it was, and also it was a constant struggle just to maintain users, etc

    This should be enough to get you thinking

  4. #4
    If it aint Dutch it aint much Kilroy's Avatar
    Join Date
    Oct 2003
    Location
    The Netherlands
    Posts
    406
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I wasn't sure which forum to post it in, but I figured it's a design issue, so I'd post it here. Well, I guess it belongs in the database forum, yes

    I've thought about it and I think it would indeed better to use varchar, however I am not sure about what the max length should be. For first and last name, I think 30 should be sufficient? I don't know names as long as that... For a country, it's probably some more, as there are quite long country names around (can't think of any examples now but there are, anyway).

    I've made the username unique, and added an email, which is also unique, and probably a good thing, too. It would of course prevent users from registering twice, which would be unwanted.

    About the roles and permissions based authentication: I am not sure if I understand you, but would it be something like this:

    groups
    gid -> mediumint, primary key, auto_increment, unsigned
    view_something -> tinyint (1)
    edit_something -> tinyint (1)

    I think that's what you mean, something like group X can do Y but not Z etc...

    Of course then I'd have a gid in the users table

    Thanks for the help

    Kilroy

  5. #5
    simple tester McGruff's Avatar
    Join Date
    Sep 2003
    Location
    Glasgow
    Posts
    1,690
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  6. #6
    Non-Member
    Join Date
    Jan 2004
    Location
    Planet Earth
    Posts
    1,764
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is an old SQL dump I have for you to look at

    Code:
    #
    # Table structure for table 'tbl_permissions'
    #
    
    CREATE TABLE tbl_permissions (
      fld_id smallint(6) NOT NULL auto_increment,
      fld_perm_name varchar(64) NOT NULL default '',
      PRIMARY KEY (fld_id)
    ) TYPE=MyISAM;
    
    #
    # Dumping data for table 'tbl_permissions'
    #
    
    INSERT INTO tbl_permissions VALUES (1,'Add Users');
    INSERT INTO tbl_permissions VALUES (2,'Modify Users');
    INSERT INTO tbl_permissions VALUES (3,'Remove Users');
    INSERT INTO tbl_permissions VALUES (4,'Show Users');
    INSERT INTO tbl_permissions VALUES (5,'All Users');
    INSERT INTO tbl_permissions VALUES (6,'Search Users');
    INSERT INTO tbl_permissions VALUES (7,'Query Roles');
    INSERT INTO tbl_permissions VALUES (8,'Add News');
    INSERT INTO tbl_permissions VALUES (9,'Modify News');
    INSERT INTO tbl_permissions VALUES (10,'Remove News');
    INSERT INTO tbl_permissions VALUES (11,'Show News');
    INSERT INTO tbl_permissions VALUES (12,'All News');
    INSERT INTO tbl_permissions VALUES (13,'Search News');
    
    #
    # Table structure for table 'tbl_roles'
    #
    
    CREATE TABLE tbl_roles (
      fld_id smallint(6) NOT NULL auto_increment,
      fld_role_name varchar(64) NOT NULL default '',
      PRIMARY KEY (fld_id)
    ) TYPE=MyISAM;
    
    #
    # Dumping data for table 'tbl_roles'
    #
    
    INSERT INTO tbl_roles VALUES (1,'Administrator');
    INSERT INTO tbl_roles VALUES (2,'Editor');
    INSERT INTO tbl_roles VALUES (3,'Author');
    
    #
    # Table structure for table 'tbl_roles2perms'
    #
    
    CREATE TABLE tbl_roles2perms (
      fld_perm_id smallint(6) NOT NULL default '0',
      fld_role_id smallint(6) NOT NULL default '0',
      PRIMARY KEY (fld_perm_id,fld_role_id)
    ) TYPE=MyISAM;
    
    #
    # Dumping data for table 'tbl_roles2perms'
    #
    
    INSERT INTO tbl_roles2perms VALUES (1,1);
    INSERT INTO tbl_roles2perms VALUES (2,1);
    INSERT INTO tbl_roles2perms VALUES (3,1);
    INSERT INTO tbl_roles2perms VALUES (4,1);
    INSERT INTO tbl_roles2perms VALUES (4,2);
    INSERT INTO tbl_roles2perms VALUES (5,1);
    INSERT INTO tbl_roles2perms VALUES (5,2);
    INSERT INTO tbl_roles2perms VALUES (6,1);
    INSERT INTO tbl_roles2perms VALUES (6,2);
    INSERT INTO tbl_roles2perms VALUES (7,1);
    INSERT INTO tbl_roles2perms VALUES (8,2);
    INSERT INTO tbl_roles2perms VALUES (9,2);
    INSERT INTO tbl_roles2perms VALUES (11,2);
    INSERT INTO tbl_roles2perms VALUES (12,2);
    INSERT INTO tbl_roles2perms VALUES (13,2);
    
    #
    # Table structure for table 'tbl_users2roles'
    #
    
    CREATE TABLE tbl_users2roles (
      fld_user_id smallint(6) NOT NULL default '0',
      fld_role_id smallint(6) NOT NULL default '0',
      PRIMARY KEY (fld_user_id,fld_role_id)
    ) TYPE=MyISAM;
    
    #
    # Dumping data for table 'tbl_users2roles'
    #
    
    INSERT INTO tbl_users2roles VALUES (1,1);
    Any questions, then get back to this thread

  7. #7
    Non-Member
    Join Date
    Jan 2004
    Location
    Planet Earth
    Posts
    1,764
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Good choice McGruff

    Don't personally have the book, though most, if not all of O'Reillys books are well worth their money, they know what they're doing

    Do you have this book btw ?

  8. #8
    If it aint Dutch it aint much Kilroy's Avatar
    Join Date
    Oct 2003
    Location
    The Netherlands
    Posts
    406
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks McGruff, that will be a very interesting read! I'll quickly go through it, as I don't have a lot of time, but I think I'll be able to pick up the general ideas

    Widow Maker: thanks for the example, I understand what you mean! It's bascially the idea that the user himself can add user groups/types/ranks/whatever you want to call it him or herself, right?

    Thanks to the both of you for helping me out,

    Kilroy

  9. #9
    If it aint Dutch it aint much Kilroy's Avatar
    Join Date
    Oct 2003
    Location
    The Netherlands
    Posts
    406
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For if you are interested, here's what came out as a final result :

    Code:
    #
    # Table structure for table 'kcms_users'
    #
    
    CREATE TABLE kcms_users (
      uid smallint(6) auto_increment,
      username varchar (50), 
      first_name varchar (30),
      last_name varchar (30),
      email varchar (100),
      country varchar (50),
      age tinyint,
      password varchar(255),
      activated enum ('Yes', 'No'),
      signup_date date,
      avatar text,
      signature text,
      role_id smallint(6),
      PRIMARY KEY (uid),
      UNIQUE (username),
      UNIQUE (email)
    ) TYPE=MyISAM;
    
    #
    # Table structure for table 'kcms_content'
    #
    
    CREATE TABLE kcms_content (
      id smallint(6) auto_increment,
      uid smallint(6),
      cid smallint(6),
      title text,
      frontpage tinyint(1),
      header text,
      date_entered date,
      meta_info text,
      content text,
      state enum ('live', 'development'),
      PRIMARY KEY (id)
    ) TYPE=MyISAM;
    
    #
    # Dumping data for table 'kcms_content'
    #
    
    INSERT INTO kcms_content VALUES (1,1,1,'Welcome to your new KCMS website!',1,'Your new KCMS website has been installed!','','','Welcome to your new KCMS website! Please remove the install direction now and then you can add content to your website!', 'live');
    
    #
    # Table structure for table 'kcms_categories'
    #
    
    CREATE TABLE kcms_categories (
      cid smallint(6) auto_increment,
      pid smallint(6),
      title text,
      description text,
      state enum ('live', 'development'),
      PRIMARY KEY (cid)
    ) TYPE=MyISAM;
    
    #
    # Dumping data for table 'kcms_categories'
    #
    
    INSERT INTO kcms_categories VALUES (1,'','News','The latest news about your website','live');
    
    #
    # Table structure for table 'kcms_navigation'
    #
    
    CREATE TABLE kcms_navigation (
      id smallint(6) auto_increment,
      name varchar(100),
      url text,
      pid smallint(6),
      state enum('live', 'development'),
      PRIMARY KEY (id)
    ) TYPE=MyISAM;
    
    #
    # Dumping data for table 'kcms_navigation'
    #
    
    INSERT INTO kcms_navigation VALUES (1,'Home','index.php','','live');
    
    #
    # Table structure for table 'kcms_sessions'
    #
    
    CREATE TABLE kcms_sessions (
      username varchar (50),
      uid smallint(6) UNSIGNED,
      time varchar (14),
      session_id varchar (200) NOT NULL,
      guest tinyint (4)
    ) TYPE=MyISAM;
    
    #
    # Table structure for table 'kcms_permissions'
    #
    
    CREATE TABLE kcms_permissions (
      perm_id smallint(6) NOT NULL auto_increment,
      perm_name varchar(70) NOT NULL default '',
      PRIMARY KEY (perm_id)
    ) TYPE=MyISAM;
    
    #
    # Dumping data for table 'kcms_permissions'
    #
    
    INSERT INTO kcms_permissions VALUES (1,'Access CMS Panel');
    INSERT INTO kcms_permissions VALUES (2,'Add content');
    INSERT INTO kcms_permissions VALUES (3,'Edit content');
    INSERT INTO kcms_permissions VALUES (4,'Delete content');
    
    #
    # Table structure for table 'kcms_roles'
    #
    
    CREATE TABLE kcms_roles (
      role_id smallint(6) NOT NULL auto_increment,
      role_name varchar(64) NOT NULL default '',
      PRIMARY KEY (role_id)
    ) TYPE=MyISAM;
    
    #
    # Dumping data for table 'kcms_roles'
    #
    
    INSERT INTO kcms_roles VALUES (1,'Administrator');
    INSERT INTO kcms_roles VALUES (2,'Moderator');
    INSERT INTO kcms_roles VALUES (3,'Member');
    
    #
    # Table structure for table 'kcms_roles2perms'
    #
    
    CREATE TABLE kcms_roles2perms (
      perm_id smallint(6) NOT NULL default '0',
      role_id smallint(6) NOT NULL default '0',
      PRIMARY KEY (perm_id,role_id)
    ) TYPE=MyISAM;
    
    #
    # Dumping data for table 'kcms_roles2perms'
    #
    
    INSERT INTO kcms_roles2perms VALUES (1,1);
    INSERT INTO kcms_roles2perms VALUES (2,1);
    INSERT INTO kcms_roles2perms VALUES (3,1);
    INSERT INTO kcms_roles2perms VALUES (4,1);
    INSERT INTO kcms_roles2perms VALUES (2,2);
    INSERT INTO kcms_roles2perms VALUES (3,2);
    INSERT INTO kcms_roles2perms VALUES (4,2);
    
    #
    # Table structure for table 'kcms_users2roles'
    #
    
    CREATE TABLE kcms_users2roles (
      uid smallint(6) NOT NULL default '0',
      role_id smallint(6) NOT NULL default '0',
      PRIMARY KEY (uid,role_id)
    ) TYPE=MyISAM;
    
    #
    # Dumping data for table 'kcms_users2roles'
    #
    
    INSERT INTO kcms_users2roles VALUES (1,1);

  10. #10
    Non-Member
    Join Date
    Jan 2004
    Location
    Planet Earth
    Posts
    1,764
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not bad, pretty much covers everything ?

  11. #11
    If it aint Dutch it aint much Kilroy's Avatar
    Join Date
    Oct 2003
    Location
    The Netherlands
    Posts
    406
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yep

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i would urge you to go back and re-think every single TEXT column,
    and make sure you actually do need 65K for each

    also, don't arbitrarily limit VARCHARs, e.g. surname 30 might be too small,
    but on the other hand, password 255 is too large

    TIME is a reserved word
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    If it aint Dutch it aint much Kilroy's Avatar
    Join Date
    Oct 2003
    Location
    The Netherlands
    Posts
    406
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi r937,

    password 255, I wonder where I got that from? Password is an md5, I just need to know how long an md5 hash is, because that's how long I must make it.

    About the varchars, if I don't want to limit them, what else would I do, just write first_name varchar ?

    What would you recommend instead of text, and for which columns exactly?

    EDIT: found out that an md5 hash is 32 chars

  14. #14
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Kilroy
    About the varchars, if I don't want to limit them, what else would I do, just write first_name varchar ?
    You'll still need to set a length, but make absolutely sure that length is suitable for the data you will hold

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Kilroy
    What would you recommend instead of text, and for which columns exactly?
    varchar, and for all but one, maybe two of them

    first_name -> text
    last_name -> text
    country -> text
    avatar -> text [this will be a link]
    signature -> text
    title -> text
    description -> text
    title -> text
    header -> text
    meta_info -> text
    content -> text
    title -> text
    description -> text
    url -> text

    the one or two which could stay as TEXT
    are the ones which you want to allow
    to go over 255 characters, which would be
    content, and maybe meta_info if this is
    intended to hold all the meta tag stuff like
    dublin core or (shock!) a great whack of keywords
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    If it aint Dutch it aint much Kilroy's Avatar
    Join Date
    Oct 2003
    Location
    The Netherlands
    Posts
    406
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well I think header should be text as well, because it will be longer than 255, it's like a shorter version of something, with which you can click a "read more" link. But I'll make the others varchar, thanks for the advice

    How about this:

    Code:
    #
    # Table structure for table 'kcms_users'
    #
    
    CREATE TABLE kcms_users (
      uid smallint(6) auto_increment,
      username varchar(50), 
      first_name varchar(100),
      last_name varchar(100),
      email varchar(100),
      country varchar(100),
      age tinyint,
      password varchar(32),
      activated enum ('Yes', 'No'),
      signup_date date,
      avatar varchar(255),
      signature varchar(255),
      role_id smallint(6),
      PRIMARY KEY (uid),
      UNIQUE (username),
      UNIQUE (email)
    ) TYPE=MyISAM;
    
    #
    # Table structure for table 'kcms_content'
    #
    
    CREATE TABLE kcms_content (
      id smallint(6) auto_increment,
      uid smallint(6),
      username varchar,
      cid smallint(6),
      title varchar,
      frontpage tinyint(1),
      header text,
      date_entered date,
      meta_info text,
      content text,
      state enum ('live', 'development'),
      PRIMARY KEY (id)
    ) TYPE=MyISAM;
    
    #
    # Dumping data for table 'kcms_content'
    #
    
    INSERT INTO kcms_content VALUES (1,1,'Administrator',1,'Welcome to your new KCMS website!',1,'Your new KCMS website has been installed!','','','Welcome to your new KCMS website! Please remove the install directory now and then you can add content to your website!', 'live');
    
    #
    # Table structure for table 'kcms_categories'
    #
    
    CREATE TABLE kcms_categories (
      cid smallint(6) auto_increment,
      pid smallint(6),
      title varchar(100),
      description varchar(255),
      state enum ('live', 'development'),
      PRIMARY KEY (cid)
    ) TYPE=MyISAM;
    
    #
    # Dumping data for table 'kcms_categories'
    #
    
    INSERT INTO kcms_categories VALUES (1,'','News','The latest news about your website','live');
    
    #
    # Table structure for table 'kcms_navigation'
    #
    
    CREATE TABLE kcms_navigation (
      id smallint(6) auto_increment,
      name varchar(255),
      url varchar(255),
      pid smallint(6),
      state enum('live', 'development'),
      PRIMARY KEY (id)
    ) TYPE=MyISAM;
    
    #
    # Dumping data for table 'kcms_navigation'
    #
    
    INSERT INTO kcms_navigation VALUES (1,'Home','index.php','','live');
    
    #
    # Table structure for table 'kcms_sessions'
    #
    
    CREATE TABLE kcms_sessions (
      username varchar,
      uid smallint(6),
      how_long varchar(255),
      session_id varchar(255) NOT NULL,
      guest tinyint (4)
    ) TYPE=MyISAM;
    
    #
    # Table structure for table 'kcms_permissions'
    #
    
    CREATE TABLE kcms_permissions (
      perm_id smallint(6) NOT NULL auto_increment,
      perm_name varchar(100) NOT NULL default '',
      PRIMARY KEY (perm_id)
    ) TYPE=MyISAM;
    
    #
    # Dumping data for table 'kcms_permissions'
    #
    
    INSERT INTO kcms_permissions VALUES (1,'Access CMS Panel');
    INSERT INTO kcms_permissions VALUES (2,'Add content');
    INSERT INTO kcms_permissions VALUES (3,'Edit content');
    INSERT INTO kcms_permissions VALUES (4,'Delete content');
    
    #
    # Table structure for table 'kcms_roles'
    #
    
    CREATE TABLE kcms_roles (
      role_id smallint(6) NOT NULL auto_increment,
      role_name varchar(100) NOT NULL default '',
      PRIMARY KEY (role_id)
    ) TYPE=MyISAM;
    
    #
    # Dumping data for table 'kcms_roles'
    #
    
    INSERT INTO kcms_roles VALUES (1,'Administrator');
    INSERT INTO kcms_roles VALUES (2,'Moderator');
    INSERT INTO kcms_roles VALUES (3,'Member');
    
    #
    # Table structure for table 'kcms_roles2perms'
    #
    
    CREATE TABLE kcms_roles2perms (
      perm_id smallint(6) NOT NULL default '0',
      role_id smallint(6) NOT NULL default '0',
      PRIMARY KEY (perm_id,role_id)
    ) TYPE=MyISAM;
    
    #
    # Dumping data for table 'kcms_roles2perms'
    #
    
    INSERT INTO kcms_roles2perms VALUES (1,1);
    INSERT INTO kcms_roles2perms VALUES (2,1);
    INSERT INTO kcms_roles2perms VALUES (3,1);
    INSERT INTO kcms_roles2perms VALUES (4,1);
    INSERT INTO kcms_roles2perms VALUES (2,2);
    INSERT INTO kcms_roles2perms VALUES (3,2);
    INSERT INTO kcms_roles2perms VALUES (4,2);
    
    #
    # Table structure for table 'kcms_users2roles'
    #
    
    CREATE TABLE kcms_users2roles (
      uid smallint(6) NOT NULL default '0',
      role_id smallint(6) NOT NULL default '0',
      PRIMARY KEY (uid,role_id)
    ) TYPE=MyISAM;
    
    #
    # Dumping data for table 'kcms_users2roles'
    #
    
    INSERT INTO kcms_users2roles VALUES (1,1);

  17. #17
    SitePoint Guru
    Join Date
    Dec 1999
    Location
    Southampton, Hants, UK
    Posts
    672
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would think about creating some sort of timestamp on those records, especially the content one. It allows you to easily create a log of what happened when, and also will help if you want to enter some sort of expiration of documents.

    I'd also agree about the permissions thing, using groups etc...

    Back to the logging thing, have you thought about creating some sort of log table that monitors what people will be doing in the CMS, updating data, adding new data and so on. If it will be just you running it then maybe give it a miss. But if you are planning to have other people, it will be good if someone screws it up! :-)

  18. #18
    If it aint Dutch it aint much Kilroy's Avatar
    Join Date
    Oct 2003
    Location
    The Netherlands
    Posts
    406
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Daniel,

    thanks for taking the time to read the thread. I think that what you are saying would indeed be useful, because I am actually planning to have other people use it, but I don't really know how I would go about doing this. Could you maybe elaborate some more on how to log what people are doing?

    Thanks,

    Kilroy

  19. #19
    SitePoint Guru
    Join Date
    Dec 1999
    Location
    Southampton, Hants, UK
    Posts
    672
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The way I'm doing it in an application at the moment (though I am using flat xml files instead of a db) is:

    At all of the key points (for you, most likely, when a database query is issued - select, update, insert delete). Output a string containing the date, time, user, function, key id's etc.. to a text file.

    so u can look through the file and see, ah, john smith created a new document with ID## at 10:24 on tuesday.. .and so on.

    Taking this to the next step would be either adding triggers to ur database to write this to a file/other db table, or there maybe a logging method that you can turn on in ur database to record all SQL queries - though this may not cover all you want to log.

  20. #20
    If it aint Dutch it aint much Kilroy's Avatar
    Join Date
    Oct 2003
    Location
    The Netherlands
    Posts
    406
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi daniel,

    thanks for explaining! I think that would indeed be useful for a lot of people, especially when they screw up (which happens often :P ) but I don't think I will add this, yet. I first want to get a very basic version done which works but isn't really sophisticated, just to look a bit at how such an application is architected and what problems I might run into. Later, when I will concentrate more on specific features I will probably add things like logging and all that. However, thanks for the replies, I will have a look at this when I get to it .

    Greetz,

    Kilroy

  21. #21
    SitePoint Guru
    Join Date
    Dec 1999
    Location
    Southampton, Hants, UK
    Posts
    672
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    but I don't think I will add this, yet. I first want to get a very basic version done which works but isn't really sophisticated,
    it can also come in very handy when in the development stage - for debugging and such :-)

  22. #22
    If it aint Dutch it aint much Kilroy's Avatar
    Join Date
    Oct 2003
    Location
    The Netherlands
    Posts
    406
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well I understand that but I think it will be a lot more work and I've got higher priorities


Bookmarks

Posting Permissions

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