Results 1 to 3 of 3

Thread: ERD help

  1. #1
    Join Date
    Jul 2002
    Posts
    1

    ERD help

    Hi freinds

    In my app, I have some kinds of user called top user, normal user, free user. I design my ERD like this:

    Table users(username, password, fullname, kindID,....)
    Table kinds(kindID, kindName)

    Can I drop table kinds?

    Thanks
    Last edited by lvthuan; 08-05-02 at 03:09.

  2. #2
    Join Date
    Sep 2002
    Posts
    53

    Re: ERD help

    Your question is actually a pretty common one and one that I and many others have pondered. After years of doing this here is what I have found to be best. Use look up tables only when they will never need to be changed or if the application that you are designing the db for will want/need the ability to modify it (add/delete/update). Here is my theory on it.

    1) If you work in IT you already have a workload that requires 110% of your time, why make it harder.
    2) If you are 100% sure that the values will never change then it makes sense. (Nothing more frustrating then when you are constantly being asked to go in to a db to change the wording, spelling, you name it and you will inevitably encounter it.)
    3) Many times when I am defining a requirement and I know that a look up table we be beneficial I sell the client on the idea that it should be updateable by them and their application. Generally only "Update" and "Insert" functionality, another lessoned learned from clients who have taken the short cut in coding and don't remove the FK's from the joined tables.
    4) You will also hear people talk about added space to the table, normalization, ect... Blah on all that! Do you know how many DB's I have looked out where every field is NULLABLE except the primary key or how about a table that is linked to another where they both have many of the same fields... come on I know you have seen them. What kind of design is that?
    5) If you take the time to design the db properly and really think it out then you should have an efficient well modeled DB and that as a designer/developer is our ultimate goal.

    There is no absolute, 100% for sure way to design a db, it is part brains, part art, and a big part of common sense. Hope this helped...




    Originally posted by lvthuan
    Hi freinds

    In my app, I have some kinds of user called top user, normal user, free user. I design my ERD like this:

    Table users(username, password, fullname, kindID,....)
    Table kinds(kindID, kindName)

    Can I drop table kinds?

    Thanks
    KDK

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hey krusty, i agree with a large part of what you said, but one thing stuck out --
    Do you know how many DB's I have looked out where every field is NULLABLE except the primary key ... What kind of design is that?
    to me that's a perfectly valid design

    i mean, sure, i know what you're saying, a row where all the fields except the primary key are all null all at the same time is pretty suspicious

    however, there's nothing wrong with each field being nullable independent of the others

    furthermore, if the idea of an all-null-except-pk row bothers you, declare a couple check constraints, why dontcha



    rudy
    http://rudy.ca/

Posting Permissions

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