SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database schema verification please

    Hey guys...

    I'm doing work on a backend project for a group of Nashville based web designers of whic I am a part. This is potentially gonna be good for all of us and I want to make sure that my part (the backend, database and CMS) are high-class.

    I'd like you to take a look at my database schema (compared to the mockups) and tell me if I'm missing anything, or if I could do something better. I'll be using Access for right now with the option of moving it to SQL server if traffic gets too high.

    I'm gonna post everything as is without comment and I'll answer questions as they come.

    Here's the website mockup link:
    http://www.the615.com/new/

    Here's the admin link:
    http://www.the615.com/new/admin

    Nothing works just yet ('cept for some of the links on the website itself).

    Here's the db schema
    ------------------------------------------------------------
    NEWS
    ---------------------
    NewsID
    NewsHeadline
    NewsBody
    NewsDate
    NewsMemberID
    NewsDeleted Y/N (top level admin only, users cannot delete)

    EVENT
    ---------------------
    EventID
    EventHeadline
    EventBody
    EventMemberID
    EventDate
    EventDeleted Y/N (top level admin only, users cannot delete)

    PROFILE
    ---------------------
    MemberID
    MemberFirsttime Y/N (if they just signed up then they must set up their profile before their information will be displayed on the site)
    MemberName
    MemberDescription1
    MemberDescription2
    MemberEmail
    MemberPM
    MemberPMType (ICQ, AIM, Yahoo, MSN) drop-down menu
    MemberWebsite
    MemberAvatar


    PLAYGROUND
    ---------------------
    PlaygroundID
    PlaygroundImage
    PlaygroundImageName
    PlaygroundMemberID
    PlaygroundDeleted Y/N


    LOGIN
    ---------------------
    LoginMemberID
    LoginUsername
    LoginPassword
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    Don't see any glaring holes on first glance (other than what to put in if they use more than one IM function on a regular basis...), but I'll look at it closer first thing in the morning and throw up my usual brilliant insight.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    SitePoint Guru nagrom's Avatar
    Join Date
    Jul 2001
    Location
    Western CT, USA
    Posts
    803
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it looks good.

    suggestion box:
    you might want to add an "is_active" field, that holds a one or zero. Then you can disable without deleting.

    field naming is a sticky subject...and you're not doing anything WRONG, persay. I've gotten in the habit of naming primary keys "id" always. no matter what table. then foreign references in other tables, i name "theforeigntablename_id". the drawback is you have to create aliases in your query if you need the id key for something.

    will events or news *ever* be shared by two members? dig what im saying? will two members ever be associated with one event? if so, you may need some join tables in the middle.

    what if members have ICQ, AOL and Yahoo IM? you may want another table of all possible PMs, and a join table that assigns PMs to users. alternatively, you can have a field for each possibility. that breaks normalization, but may be easier to deal with.

    these are tough questions....you may end up making the SQL overly complicated if you try to normalize too far.

    where's r937 when you need him?
    Last edited by nagrom; Oct 21, 2002 at 14:14.

  4. #4
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK...to answer the first few comments.

    It's possible that a user may have more than one IM app, but the design of the site only allows for one to be displayed.

    An event might share two members or more but only one user will ever announce an event...that's what that is for.

    The is_active field has already been thought of. I'm using y/n. Same thing of course though. It's unlikely that a post will ever need to be deleted anyway but I wanted to cover my bases.

    Good comments so far guys...thank you.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  5. #5
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by nagrom
    ...what if members have ICQ, AOL and Yahoo IM? you may want another table of all possible PMs, and a join table that assigns PMs to users. alternatively, you can have a field for each possibility. that breaks normalization, but may be easier to deal with...
    What I was thinking of too; and what if a new type of IM app (MemberPMType) is introduced?

    And... a member can have more than one email, more than one website etc - and one of them is the primary email etc
    But if the rule is that only one can be displayed, that's the way things are

  6. #6
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PROFILE
    ---------------------
    MemberID
    MemberFirsttime

    Take out FirstTime (p.s. unless you really need the table name appended to each column I would drop them as they are somewhat redundant and don't help) and stick it into another table (e.g. put userIDs as the sole column of a table and delete them when they verify). No use keeping a column around which is useless after it is set to 'Y'.
    Last edited by MattR; Oct 21, 2002 at 18:44.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    >> "where's r937 when you need him?"

    i was napping

    actually, i had a look yesterday, and figured the database design was pretty good

    didn't care for the mockups (way too crowded, font size way too small), but creole didn't ask about them

    i agree the column naming convention, using the table name, is a bit clumsy -- i will often use an abbreviated prefix, and i find that a two-letter prefix works well (holler if you want to see an example of foreign keys, intersection tables, etc.)

    other than making web sites, IM and email addresses one-to-many, the only other thing i can think of is a couple of columns to help people remember forgotten passwords (challenge question/answer or reminder phrase)

    rudy

  8. #8
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can a Member have more than 1 Login? If not then I don't see the point of having the separate Login table. Just add the LoginUserName and LoginPassword fields to the Profile/Member table.

    Regarding the Profile table, why have you chosen to call it Profile and yet all the fields have the prefix 'Member'? Personally, I'd pick one name and stick with it throughout.

  9. #9
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Good point shane...I actually called the table "member" after realizing I did that. I just forgot to change the text file that I copied the schmea from.

    As for the login, that's the one that I really wasn't sure about. The only reaso I put it separate is because I thought you guys might tell me to keep it apart from other tables so that the query might run faster. If you think it's not a big deal then I'll add the login information to the "member" table.

    Anyone disagree?

    As for the table and field names, I've had people tell me that it makes it easier for them to work if you name the field after the table...helps to keep things easily identified. Do you all think it's not enough to worry about? Maybe if I just called each field in the Member table MFirstName, MEmail, etc?
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  10. #10
    SitePoint Guru nagrom's Avatar
    Join Date
    Jul 2001
    Location
    Western CT, USA
    Posts
    803
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    >> "where's r937 when you need him?"

    > i was napping

    oh geez, was i trolling?
    i was actually thinking about a post r937made to "thelist" (the evolt.org mailing list) entitled "denormalization is not a dirty word", which pertains to the questions presented here.
    http://lists.evolt.org/archive/Week-...14/124876.html

    creole, i don't think there's any performance gained by moving a few fields into their own table. you're not doing any "SELECT * FROM table" you bad boy, are you?

    and r937, you like mozilla do you not? can't you set the font size to 500% where its more comfortable for you?

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    >> you like mozilla do you not? can't you set the font size
    >> to 500% where its more comfortable for you?

    not the text images, no

  12. #12
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by r937
    >> you like mozilla do you not? can't you set the font size
    >> to 500% where its more comfortable for you?

    not the text images, no
    Well, you could use IE6 (with web accessories installed) and zoom in on the images
    Attached Images Attached Images

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    >> "you could use IE6 (with web accessories installed)
    >> and zoom in on the images"

    or, i could leave the offending site and go somewhere else

    i don't eat spicy food, either -- nothing against spicy food or the people who eat it, it's just something i really dislike and i have learned to avoid it

    web page too hard to read? i'm outta there

    font size and text-as-gif aren't the only problems that make me abandon a site, either

    but hey, let's get this back on topic, creole was asking about his database design, not his web design...

    rudy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not worried about it Rudy. I didn't design it...I'm just doing the coding. While it's not my favorite design I think it looks pretty decent.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  15. #15
    SitePoint Guru nagrom's Avatar
    Join Date
    Jul 2001
    Location
    Western CT, USA
    Posts
    803
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    for the record, i like spicy food, but only if i'm eating it with a teeny-tiny fork.


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
  •