Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Aug 2007
    Posts
    3

    A question of Database normalization

    Before my question, I'll give a brief discription of my application.

    This is an online registration application to allow people to register and pay for any one of a set of (different types of) programs available. All of the programs have a set of common fields that the registrant has to enter.
    Few (very few) programs have a varying set of optional fields (some of which are mandatory).
    ie. Some types of programs need fields zzz and yyy. Others need aaa and bbb fields.

    Right now, I just have the common set of fields in a single Table. I also have a seperate transaction related table (if that is of any importance).

    So my question is, do I create a more 'normalised' database and put these additional fields in a seperate table, or do I just add these additional fields in the main registration table itself and use it only for those that need them?
    And if I do use a separate table, how is that to be structured?

    I'm sure this little 'problem' has come up before and someone has the perfect solution for this. I just need to know what that is

    If it is needed, I could post the table structure as I have it.

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    This is a first attempt for you but I haven't done one of these before.
    The types for each field are up to you.
    The stars indicate key fields.

    Mike

    Code:
    -- stores all your apps
    create table Apps (
       *id
       name
    )
    
    -- list of possible fields
    -- if standard_yn=Y then this field applies to all apps
    -- seq_no would decide the oprder it appears on screen
    -- assume field_name is unique.
    create table Fields(
       *field_name
       standard_yn
       seq_no
       mandatory_yn
    )
    
    -- list the non standard fields required for each app
    create table AppOptionalFields(
       *app_id
       *field_name
    )
    
    -- if the user must pick from a list of values for a field
    create table FieldValidValues(
       *field_name
       *value_code
       *value_str
    )
    
    -- the users responses for each field for each app.
    -- Need to decide if standard fields just appear once per user
    -- or are stored each time for each app.
    -- This will affect whether app_id allows null or not
    create table UserFieldValues(
       *user_id
       *field_name
       *app_id
       field_value
    )
    
    -- what ever fields you store for user
    create table Users(
       *id
       name
       email
       pwd
       ...
    )
    
    -- not sure how you store payments
    create table UserAppPayments(
       *user_id
       *app_id
       payment_date
       amount
    )

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Quote Originally Posted by rojer 31
    So my question is, do I create a more 'normalised' database and put these additional fields in a seperate table,
    I reckon I'd run with that idea. This sounds like a situation where a 1:1 relationship should be maintained in more than one table.
    George
    Home | Blog

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rojer_31
    So my question is, do I create a more 'normalised' database and put these additional fields in a seperate table, or do I just add these additional fields in the main registration table itself and use it only for those that need them?
    And if I do use a separate table, how is that to be structured?

    I'm sure this little 'problem' has come up before and someone has the perfect solution for this. I just need to know what that is
    there is no "perfect" solution, otherwise it would already be out there in the marketplace with a hefty price tag (and perhaps a free 30-day download)

    what you have is almost a classic example of a subtype/supertype model (do a search)

    your two main options:

    1. a single common table with all columns (some of which must be NULLable)

    2. a single common table (supertype) with additional related (subtype) tables

    the "tipping point" between these two approaches is the ratio of the number of common columns to the number of unique columns

    if there are but a few unique columns, use option 1

    under no circumstances should you contemplate a design where you store a field name in a column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    The other big consideration is the frequency with which new optional fields are defined. If you are going to be frequently adding new programs then you need a dynamic solution. Mike has suggested an EAV model. Another option is to use an XML column in the common table and avoid subtables altogether.
    None of these solutions is perfect. All of the are awkard to implement in some way or another. Which you choose depends upon the nature of your data.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Originally posted by Rudy
    under no circumstances should you contemplate a design where you store a field name in a column
    The benifit of following Rudy's advice is being able to use foreign keys which will allow the database to screen bad data at a low level.

    The only disadvantage is you'll just need to alter the structure of your database each time you have a new field for an existing application or a new application. This would involve either adding a new field to a table or creating a new table completely depending on your design. Obviously you'll need to redo all your application code as well to cope with these changes.

    If you're happy doing all this work then this is definitely the way to go. I personally prefer systems which are compact, quick to write and are designed to cope with future changes. Up to you really

    Mike

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mike_bike_kite
    Obviously you'll need to redo all your application code as well to cope with these changes.
    that statement is inflammatory, alarmist, and fearmongering

    there's no difference between adding a new column to a subtype/supertype model than to any other model -- only the affected portions of the app are affected
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    that statement is inflammatory, alarmist, and fearmongering
    ... and true - unless I misunderstand your method.

    If you're using new database fields to hold fields for the applications then this involves structural change. If you have structural change in the database then this needs to be matched with relevant changes in the application.

    I don't view my solution as EAV (and all the attendant flaming that goes with it) - I'm simply storing the field name in a table which has a many to one relationship with the application. Generic yes - EAV no.

    Mike

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It's EAV without the E.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by r937
    only the affected portions of the app are affected
    No tautological arguments, please!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman
    No tautological arguments, please!
    ordinarily, i would agree with you, but in this instance i was contrasting only affected portions with all your application code

    everyone who knows how a VIEW works will understand



    no offence intended, i presume everyone here knows how a VIEW works
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    I would say that everyone who understands would understand.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that goes without saying, eh



    um, wait a sec...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    So if we use your approach to add a field to a new application we just :
    • create a new table for the application
    • add new fields when they're required
    • alter the view to incorperate each new field
    While my "AV" approach would be
    • insert AppOptionalFields values ($my_app_id,'new_field')
    I'm not sure I'm convinced yet

    Also if you had 500 applications would you need 500 tables to hold the data? I'm not sure this would be very performance friendly.

    Mike

    EDIT: Added point about performance
    Last edited by mike_bike_kite; 08-20-07 at 13:05.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    who was that post in response to?

    whose approach would add a table for an application? certainly not mine

    i have a data-centric viewpoint towards database modelling, not an application-centric viewpoint

    and look, please, i am certainly not out to convince you (i realize how much of an uphill battle that is, and i refuse to do it)
    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
  •