Development Proper Database Layout Methods?

Discussion in 'Software' started by OneSeventeen, 15 May 2007.

  1. OneSeventeen

    OneSeventeen Oooh Shiny!

    Joined:
    3 Apr 2002
    Posts:
    3,454
    Likes Received:
    2
    When I make a database layout I always ask myself what I am trying to keep track of, and how can I use the least amount of space to store it all. (probably a bad approach?)

    I want to be able to glance at a field name and know what table it is from, and what type of data it contains. (Once again, probably a bad approach)

    For example, let's say I only want to keep track of usernames, passwords, and the user's favorite websites, and for fun, what type of user this is:

    • tb_users
      • user_id (unsigned int, auto-incrementing)
      • user_login (varchar 25 characters)
      • user_pass (varchar 255 characters... encrypted)
      • usertype_id (unsigned int)

    • lk_usertypes
      • usertype_id (unsigned int, auto-incrementing)
      • usertype_label (varchar 25 characters)
      • usertype_description (text)

    • tb_userwebsites
      • userwebsite_id (unsigned int, auto-incrementing)
      • userwebsite_url (text)
      • userwebsite_title (varchar 255 characters)
      • userwebsite_approved (boolean true|false)
      • user_id (unsigned int)

    The purpose of this layout being I can easily glance at the tb_userwebsites and know that it is a table full of user websites, and can see that it is tied to the tb_users table via user_id.

    Before anyone posts:
    I know this is a bad way to lay out a database, which is why I'm posting, so please be nice :D

    I have been told tables should always be singular, and shouldn't have a prefix. I've also been told field names should not have a prefix.

    I just don't want to wind up with "job_code" as a field name in 2 different tables with 2 different meanings, because that would just confuse and annoy me later on down the road.

    I also want to make bringing tables together in joins very easy without using a GUI, as most of my stuff is done in MySQL or PostgreSQL.

    So, is there a good, easy to follow tutorial on some coding standard for laying out a database that might tell me why I should only use singular table names, and why prefixes are bad?

    I love having my table types sorted in the list (I loosely use the terms tb, lk, and br as table, lookup, and bridge respectively), and also like to know what type of data to expect from it. (meaning lk's rarely change, tables change constantly, and bridges connect data for many-to-many relationships and whatnot)

    Tips? Tutorials? Links?
     
  2. DougEdey

    DougEdey I pwn all your storage

    Joined:
    5 Jul 2005
    Posts:
    13,933
    Likes Received:
    33
    if something has a different meaning call it something else.

    For instance, in a set of pseudo anonomised tables I use, each has an eff_date (effective date) and an end_date (end of that record), but one table has an entry_date and end_date and another has eff_date and exit_date, everything named eff_date has the same meaning at a glance.

    Singular table names are used because a good database definition should never have a many to many relationship, one to many and one to one is good, m-m is very bad. It means you'll have weird keys, searching is longer and it gets confusing. It's something I've been taught in my 2nd year at uni.

    If you're doing joins it's easier and clearer to write LEFT JOIN <table> USING (commonField) then LEFT JOIN <table> WHERE field1=field2

    Prefixes make it harder to view things at a glance, you're duplicating the name of the table again when you shouldn't need to. i.e in a query you'll have table.table_id rather then table.id

    For us at work, we have a complete database isolated as reference_tables, then in there common reference tables are unmarked but some specific ones have comments.
     
  3. OneSeventeen

    OneSeventeen Oooh Shiny!

    Joined:
    3 Apr 2002
    Posts:
    3,454
    Likes Received:
    2
    so in my example, what would be a good layout?

    maybe this:

    user
    uid
    username
    password
    usertypeid

    usertype
    utid
    label
    description

    userwebsite
    uwsid
    url
    title
    approved
    uid

    but then if I added a table with categories would it be bad to have a field called "label" and "description" since I've already used those field names in the usertype table?
     
  4. DougEdey

    DougEdey I pwn all your storage

    Joined:
    5 Jul 2005
    Posts:
    13,933
    Likes Received:
    33
    I would say change "uid" to userid, in this case it's acceptable because otherwise you have a 3 letter abrreviation, I would also say that utid may be better called code or similar as it's not really a unique identifier, i.e. you can group people by it.

    With your category table, label and description would be fine as the usertype table is a category type table, so if you keep a uniform style to your category or reference tables then it makes it easier to switch between them.

    None of the stuff I have said would have made any sense to me before I started dealing with millions (actually just over 150million now) of records.

    This process makes it easier if you intend to use views as well.
     
  5. koola

    koola Minimodder

    Joined:
    11 Jul 2004
    Posts:
    2,401
    Likes Received:
    10
    Just have "uid" as "pid" (primary key) then change "utid" in tbl usertype to fid (foeign key, no primary) - no need for a primary key here as it's a 1-1 relationship.

    Change "uwsid" to "pid" (primary key) and then change "uid" to "fid" (tbl user foreign id).

    When selecting records, you can do something like this:

    "SELECT user.username, userwebsite.pid, userwebsite.url FROM user a, userwebsite b WHERE a.pid = b.fid"

    or if you drop the f and p from id, you can use the "USING({id})" as Doug has mentioned.
     
  6. DougEdey

    DougEdey I pwn all your storage

    Joined:
    5 Jul 2005
    Posts:
    13,933
    Likes Received:
    33

    EWW! Don't do that do

    "SELECT user.username, userwebsite.pid, userwebsite.url FROM user a LEFT JOIN userwebsite b WHERE a.pid = b.fid"
     
  7. koola

    koola Minimodder

    Joined:
    11 Jul 2004
    Posts:
    2,401
    Likes Received:
    10
    What's wrong with the simple join? Why the LEFT JOIN?
     
  8. DougEdey

    DougEdey I pwn all your storage

    Joined:
    5 Jul 2005
    Posts:
    13,933
    Likes Received:
    33
    LEFT JOIN ensures that values match, it's more for ease of reading and it makes it marginally faster.
     
  9. koola

    koola Minimodder

    Joined:
    11 Jul 2004
    Posts:
    2,401
    Likes Received:
    10
    Wouldn't the LEFT JOIN just return all usernames from tbl user even if they have no url. If I remember 2nd year db design correctly (probs not), it will return all records that match as well as an extra record for each unmatched recorded in the left joined table, and this is faster...?
     
  10. RTT

    RTT #parp

    Joined:
    12 Mar 2001
    Posts:
    14,120
    Likes Received:
    74
    What's with the ridiculously complex naming scheme? pid? swid? asdadid? You're making your queries harder to read than they need to be.

    user
    id
    username
    password
    user_type_id

    user_type
    id
    label
    description

    user_website
    id
    url
    title
    approved
    user_id

    this reads easier:

    SELECT user.username, user_website.id, userwebsite.url
    FROM user LEFT JOIN user_website
    WHERE user_website.user_id = user.id

    than:

    SELECT user.username, userwebsite.pid, userwebsite.url
    FROM user a LEFT JOIN userwebsite b
    WHERE a.pid = b.fid

    (why would pid = fid? what is f? what is p? (it's redundant info, that's what))
     
  11. OneSeventeen

    OneSeventeen Oooh Shiny!

    Joined:
    3 Apr 2002
    Posts:
    3,454
    Likes Received:
    2
    RTT's method is what I started with, I honestly can't remember why I changed...

    Are there any documents on database design standards?

    While I don't really plan on using any code-generators, I do plan on working with other people on projects, and just want to make sure that the way I'm doing things is as standard as possible.

    (Thanks for reaffirming my thoughts that my previous method was too long winded)
     
  12. DougEdey

    DougEdey I pwn all your storage

    Joined:
    5 Jul 2005
    Posts:
    13,933
    Likes Received:
    33
    How about making a document that is signed off by your department for standard ways of doing things?
     
  13. koola

    koola Minimodder

    Joined:
    11 Jul 2004
    Posts:
    2,401
    Likes Received:
    10
    pid = primary key, fid = foreign key. Hardly redundant is it ;) Keeps things cleaner as easier to read tbh.

    Try the DB language website or have a google.
     
  14. RTT

    RTT #parp

    Joined:
    12 Mar 2001
    Posts:
    14,120
    Likes Received:
    74
    It is redundant - user_website.user_id is blatantly an FK, and if you keep to just 'id' for PKs, then user.id is obviously a PK... Not sure why you need a whole prefix on the field name when you can stay away from prefixes and still know what you need to.

    Whatever though. Just stay away from my databases ;)
     
  15. RTT

    RTT #parp

    Joined:
    12 Mar 2001
    Posts:
    14,120
    Likes Received:
    74
    For naming conventions (you don't mean database design), probably. And there are probably quite a few that all conflict with each other and want to convert you to their "correct" method. Just do whatever you are comfortable with and stick to it across any databases you design.

    It's hardly an important issue as long as you aren't screwing yourself or anyone else over who may happen to inherit the system if you drop dead.

    What is FAR more important is database design - getting that bit right is paramount.
     

Share This Page