Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Sep 2008
    Posts
    33

    Simple db design input needed

    Hi,

    I'm a new member and also a beginner when it comes to relational db design. I'm currenly making a journal system as a simple exercise and I'm having trouble deciding where to place some of the information/fields. What I would like someone to do is to check out my design (attached jpg) and give me some input on the practicality of my design. Should I move any of the columns from one table to another (E.g. should I delete the table T_Contact and instead put the fields email and phone in the table T_Patient?) How far is it meaningful to normalize the db?

    I'd be grateful for any input.

    /Kris
    Attached Thumbnails Attached Thumbnails tables.jpg  

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Why are all the non-key columns nullable?

    Why do you have phone numbers without names? There's apparently no way to associate a phone number with a patient.

    Quote Originally Posted by kristofer
    Hi,
    How far is it meaningful to normalize the db?
    /Kris
    Aim to achieve Fifth Normal Form unless you have good reasons to do otherwise. Only you know your data though, we can't design it for you in a forum.

  3. #3
    Join Date
    Sep 2008
    Posts
    33
    Quote Originally Posted by dportas
    Why are all the non-key columns nullable?
    Pure forgetfullness, I had the schema on paper and dl the first design tool I could find to be able to post a picture in here. I guess I was a bit sloppy.

    Quote Originally Posted by dportas
    Why do you have phone numbers without names? There's apparently no way to associate a phone number with a patient.
    Why not? If I relate T_Patient with T_Contact then there's an obvious association, isn't there?

    Quote Originally Posted by dportas
    Aim to achieve Fifth Normal Form unless you have good reasons to do otherwise. Only you know your data though, we can't design it for you in a forum.
    Thanks. And yes I know, I'm not asking you to do my design for me. The question of how far normalization is practical seems to be contended. I've seen several proponents arguing that 3NF is enough for real world applications and I was curious to see what the members of this forum thought about this.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    What's with the naming convention T_?If you're going to prefix all your table names with that, you might as well be consitant and rename your columns in the same fashion:
    T_User ( C_UserID, C_Name, C_Role )

    Seriously, this notation is redundant and pants
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Quote Originally Posted by kristofer
    I've seen several proponents arguing that 3NF is enough
    When the accelerator got jammed on in my dads Morris Minor he coasted to a stop, turned of the engine and popped both the boot and the bonnet.

    From the bonnet he produced one of his trainers and proceeded to take the lace out. He then replaced the broken accelerator return spring with the aforementioned shoelace.

    It was enough to get us to the nearest service station, but that's not the point is it
    Last edited by gvee; 09-16-08 at 06:01. Reason: Remembered what that spring was called :)
    George
    Home | Blog

  6. #6
    Join Date
    Sep 2008
    Posts
    33
    Quote Originally Posted by georgev
    What's with the naming convention T_?If you're going to prefix all your table names with that, you might as well be consitant and rename your columns in the same fashion:
    T_User ( C_UserID, C_Name, C_Role )

    Seriously, this notation is redundant and pants
    Well I'd like to use address (instead of addressLine) as a column in the table Address and thought it would be prudent to avoid confusing queries by having to write statements such as select address from address... and it's easier to use a prefix for the table once instead of adding prefixes to every column.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    That's another point: is varchar(40) enough for address?

    Tables rarely have a single record in them, meaning they are collections... So I tend to make their names plurals of what they contain.

    So that table would become "addresses", just as I'd rename your T_User to be called "users". This also removes the apparent confusion from your query.
    Code:
    SELECT address
    FROM   addresses
    Makes sense to me - read the query aloud if you don't believe me
    George
    Home | Blog

  8. #8
    Join Date
    Sep 2008
    Posts
    33
    Quote Originally Posted by georgev
    That's another point: is varchar(40) enough for address?
    Well, I live in Sweden and I find it hard to believe that there's an address exceeding the length of 40 characters here, but I can see how it would be a issue for someone living in the UK (Welsh local names can be pretty long from what I've heard.)

    Quote Originally Posted by georgev
    Tables rarely have a single record in them, meaning they are collections... So I tend to make their names plurals of what they contain.

    So that table would become "addresses", just as I'd rename your T_User to be called "users". This also removes the apparent confusion from your query.
    Code:
    SELECT address
    FROM   addresses
    Makes sense to me - read the query aloud if you don't believe me
    Oh, I believe you alright. The prefix T for table is what I use after watching Learnkey's Database specialist design course. If using this convention results in people feeling discomfort/annoyance I will of course change it.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by kristofer
    I've seen several proponents arguing that 3NF is enough for real world applications and I was curious to see what the members of this forum thought about this.
    Just referring to your answer on another thread - 3NF is the prevailing opinion here. I am leaning more and more towards 5NF as time goes on.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    True enough, I was too hasty in using my Morris Minor story. 3NF is acceptable, but why not strive for something higher

    Mr Achivist, do you have a link to that naming ocnventions discussion from a few months back?

    It's a highly opinionated subject, and there is no single correct answer. The blanket statement is "consistency is key", but it's very loose in it's definition.

    I'm simply offering up my 2cents, doesn't mean you have to take it
    George
    Home | Blog

  11. #11
    Join Date
    Sep 2008
    Posts
    33
    Quote Originally Posted by georgev
    True enough, I was too hasty in using my Morris Minor story. 3NF is acceptable, but why not strive for something higher
    It's a good story and I'm busy as blazes reading up on 4NF and 5NF. Of course, statements like "There must be no non-trivial join dependencies that do not follow from the key constraints. A 4NF table is said to be in the 5NF if and only if every join dependency in it is implied by the candidate keys." doesn't exactly help with the understanding

    Is “beyond 3NF” unnecessary? It might be, but probably in many commercial situations it is
    unnecessary. Remember that application SDKs are just as powerful as database engine structural and
    functional capabilities. Extreme implementation of normalization using layers beyond 3NF tends to
    place too much functionality into the database. Why not use the best of both worlds—both database
    and application capabilities? Use the database to store data and allow applications to manipulate and
    verify data to a certain extent.
    -- Powell, Beginning Database Design
    Last edited by kristofer; 09-16-08 at 10:41.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    I'd agree taken to its extremities it is a wee bit silly...... however I do think there is a role for identifying what are queries and what are tables, unless you deliberately want to obfuscate whether its a query or a table.

    The Hungarian notation was I suspect more of a This is the way real programmers using C do it (because of the well documented need for such a convention in C because of its inherent design) and therefore toy programmers in Basic MUST go the same route.

    it would be silly to subtype the column names
    eg
    intID integer autonumber
    strName string

    ..especially as with a good column naming convention you can (shoudl) be able to deduce the datatype form its descriptive name.

    ..it also helps when on a bughunt based on user error report to hear them cite its frmMain, or rptWhatADoozy.. but thats within the confines of Access.. I don't pull such stunts in other environments.
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    In a legacy framework we had a table called tblUsers - it stored current users information and there were several child tables storing the users' permissions etc.

    When a user account was no longer needed it was removed from tblUsers and added to tblExUsers. Now, what happens when a user account was then needed to be re-enabled (e.g. someone came back to the company, or the account was mistakelnly deleted) - that's right, all permissions and settings were lost because of the previous delete.

    Now, because of the way the legacy framework was designed, we couldn't have disabled accounts knocking around in tblUsers, so we had to improvise.

    The solution was to change tblUsers and add a "is_deleted" boolean field, and rename tblUsers to something else; in this case "users".

    So that the legacy framework knew no better, a view was created of this table of all columns excluding the is_deleted oclumn, with a where clause leaving only active accounts... and the view was called tblUsers.
    George
    Home | Blog

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by healdem
    ... unless you deliberately want to obfuscate whether its a query or a table.
    obfuscation is the wrong word

    i think a better phrasing would be "unless you deliberately want to take advantage of sql's orthogonality, which of course was why sql was designed the way it was, on purpose"

    it would be silly to subtype the column names
    no more or no less silly than typing the tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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