#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2014
    Posts
    11
    Rep Power
    0

    SQL Tables for simple CF CRUD App


    Doing a simple crud app for storing passwords at work. (Yes, using SQL stored procedure to encrypt and decrypt passwords).

    Hoping I could get some advice on by SQL table structure.

    Basically 4 tables.

    tbl_DevNam
    pk RecID
    DevName

    tbl_Application
    pk RecID
    AppName

    tbl_AcctName
    pk RecID
    AcctName

    tbl_Junction
    pk RecID
    fk DevName
    fk AppName
    fk AcctName
    varBinary PWord

    The first three of course are basically look up tables with tbl_Junction tying the many to many relationships together.

    Nothing fancy, just entering and viewing data.

    Thanks
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,307
    Rep Power
    971
    The one thing I would stop and fix first is your naming. There's no reason to introduce potential confusion with abbreviated table or column names. Good names always trump saving a couple of keystrokes. Perfect example: I have no idea what DevNam is supposed to mean, and can think of six possible meanings right off the top of my head.

    • I'd change tbl_AcctName to AccountName
    • I'd change RecId to recordId (or just make it easy on everyone and use id).
    • etc.


    Hungarian notation (e.g. tbl_) is unnecessary for tables. And while it's personal preference, I wouldn't use full camel case for column names (tables may use full camel case, but columns generally use headless camel, e.g. myColumnName, or underscores, e.g. my_column_name).
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2014
    Posts
    11
    Rep Power
    0
    Thanks, I was basically following the way we currently do data bases here at work, but I agree with you on simplifying the names etc. I take you're OK with the basic table structure though, correct?
  6. #4
  7. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,307
    Rep Power
    971
    Other than the naming, it looks fine to me.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2014
    Posts
    11
    Rep Power
    0
    Thanks.

    Quick question, from a best practices standpoint, do you use cfquery in your apps, or calls to stored procedures? Since one of the basics of this app is going to be entering a device name and getting a display with all the associated applications, services accounts, and their passwords, for the little bit I'm doing here, shouldn't I just add that to the stored procedure that decrypts the passwords to begin with?
  10. #6
  11. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,307
    Rep Power
    971
    I just use cfquery. In most cases, stored procedures are overkill. Plus, I really don't want my business logic split up between the web app and stored procs. That said, I'm not sure what you mean by "add it to the procedure that decrypts the passwords". I wouldn't just tack on unrelated queries for other data to a decryption procedure. I'd add another procedure (if I used stored procedures, which I don't).

IMN logo majestic logo threadwatch logo seochat tools logo