Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Posts
    45

    Unanswered: Table Design - 3rd Normal Form?

    Is this the way the data should be stored?
    Consider that multiple date ranges will be seated for key fields 1-6

    * = Key

    Table1:

    *FIELD1 CHAR(10)
    *FIELD2 CHAR(10)
    *FIELD3 CHAR(10)
    *FIELD4 CHAR(10)
    *FIELD5 CHAR(10)
    *FIELD6 CHAR(10)
    *HIGHDATE1 DATETIME
    HIGHDATE1 DATETIME
    *HIGHDATE2 DATETIME
    HIGHDATE2 DATETIME
    UNQUE_ID INT(4)

    Or should it be 2 tables be like this:

    Table1:

    *FIELD1 CHAR(10)
    *FIELD2 CHAR(10)
    *FIELD3 CHAR(10)
    *FIELD4 CHAR(10)
    *FIELD5 CHAR(10)
    *FIELD6 CHAR(10)
    UNQUE_ID INT(4)

    Table2:
    *UNQUE_ID INT(4)
    *HIGHDATE1 DATETIME
    HIGHDATE1 DATETIME
    *HIGHDATE2 DATETIME
    HIGHDATE2 DATETIME

    This has become a hot issue in the office, So Im welcoming many replies to this. I want the answer to be more of a general consensus, than just another opinion.

    *Also is the first example 3rd normal form?
    *If so whats the 2nd example? (4th?)

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How come you have HIGHDATE1/2 twice?

    How can you do that?

    And the "Unique Id" is a waste...

    Seems like you have a natural key...use it...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Dec 2002
    Posts
    45
    Originally posted by Brett Kaiser
    How come you have HIGHDATE1/2 twice?

    How can you do that?

    And the "Unique Id" is a waste...

    Seems like you have a natural key...use it...
    Highdate1 = Booking Date
    Highdate2 = Departure Date

    Together they create a rule /record.

    Unique Id is used to join between all other tables that have the key on table1. I haven't included the rest of the tables in the design because they arn't relevant to the question.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "Consider that multiple date ranges will be seated for key fields 1-6"

    If you can have multiple booking/departure dates for a given set of six
    CHAR(10) fields, then definitiely go with option 2. And go ahead and use your surrogate key rather than trying to join on six character columns.

    blindman

  5. #5
    Join Date
    Feb 2002
    Posts
    2,232
    Can you explain in greater detail what each column means and submit a sample set of data ? And are you using these columns in queries that require comparisons ? If so, what is the granularity - day/hour/minute/second ?

Posting Permissions

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