Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Apr 2008
    Posts
    7

    Unanswered: HOW TO CREATE TABLES from a given FLAT FILE

    Hi guys,I do understand how to create a Table from Microsoft Access,what i dont understand is when you given a FLAT FILE,and expected to come up with TABLES.

    Here is an EXAMPLE
    1.The FLAT FILE has 11 ATTRIBUTES as follows:
    CustomerID,LastName,FirstName,Phone,Gender,GenderN ame,MemberSkill,MemberCost,CountryCode,CountryName and Hobbies.

    2.When I come up with Table is as follows:
    Customer Table,
    -CustomerID
    -LastName
    -FirstName
    -Phone

    Gender Table-Genders
    -GenderName

    Member Table-MemberSkill
    -MemberCosts

    Country Table-CountryCode
    -CountryName

    Hobby Table
    -HobbyCode
    -HobbyName

    3.How can i create relationships(ERD-Entity Relationship Diagram) between those TABLES above?.I find it very difficutly,I have repeated over and over still not successful.DOES ANYBODY KNOW WHAT I MISS,Does it mean i have to introduce other fields inside some TABLES,so that i can create somekind of proper Primary key,for instance Table like Country,which is Primary key is kind imposible at present.

    4.I do understand every table in Microsoft Access must have Primary Key.But when comes to create ERD-Entity Relationship Diagram,some PK doesnt agree,I am stack with it,can someone kindly unfold something i cannot see here

    Thank you in Advance,
    P

  2. #2
    Join Date
    Apr 2008
    Posts
    7

    Creating Relationship On Tables

    Hi guys,I do understand how to create a Table from Microsoft Access,what i dont understand is when you given a FLAT FILE,and expected to come up with TABLES.

    Here is an EXAMPLE
    1.The FLAT FILE has 11 ATTRIBUTES as follows:
    CustomerID,LastName,FirstName,Phone,Gender,GenderN ame,MemberSkill,MemberCost,CountryCode,CountryName and Hobbies.

    2.When I come up with Table is as follows:
    Customer Table,
    -CustomerID
    -LastName
    -FirstName
    -Phone

    Gender Table-Genders
    -GenderName

    Member Table-MemberSkill
    -MemberCosts

    Country Table-CountryCode
    -CountryName

    Hobby Table
    -HobbyCode
    -HobbyName

    3.How can i create relationships(ERD-Entity Relationship Diagram) between those TABLES above?.I find it very difficutly,I have repeated over and over still not successful.DOES ANYBODY KNOW WHAT I MISS,Does it mean i have to introduce other fields inside some TABLES,so that i can create somekind of proper Primary key,for instance Table like Country,which is Primary key is kind imposible at present.

    4.I do understand every table in Microsoft Access must have Primary Key.But when comes to create ERD-Entity Relationship Diagram,some PK doesnt agree,I am stack with it,can someone kindly unfold something i cannot see here

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Although I might have separate tables (for lookup tables) as you've diagramed, I would still keep those fields the Customer table (except for maybe the Hobby table where there 1 customer could have many hobbies.)

    For example, think about if you were asked to create a query which returned the: LastName, FirstName, Phone, GenderName, MemberCosts, and CountryCode. One query to return all this information entails linking in 4 tables verses 1 table to return all the information. Now say you had a million or so records. That query with all the linked tables will be slow and you get into a "spider-web" design (among complicating other things like data entry forms).

    You should separate tables when there would be many records for 1 record in another table (i.e. perhaps your Hobby table) and not just because data would be duplicated in 1 table (otherwise as an example for a table which had a Name, Address, City, State, Zip fields - developers would separate City, State, and Zip in separate tables which would be a NIGHTMARE regularly returning (and entering) a customer's Name and full address). Over-Normalizing a database can be a bad thing.

    CustomerDataTable:
    CustomerID (assuming this is your autonumber field.)
    LastName
    FirstName
    Phone (IF there were multiple phone numbers for 1 customer, I would probably then put phone numbers/types in a relational table similar to the Hobby's relational table.)
    Gender
    GenderName (although this field could eliminated and be as a lookup table but again think about having to link the GenderLookupTable every time just to get the GenderName. Instead of a Gender field in this table, I might consider just having a GenderName field.)
    MemberSkill
    MemberCost
    CountryCode
    CountryName (this field also could be eliminated and as a lookup table but again think about having to link the CountryLookupTable every time just to get the CountryName.)

    HobbyDataTable (assuming many hobbies for 1 customer):
    HobbyID (your autonumber field.)
    CustomerID (integer field - linked to CustomerID in CustomerDataTable)
    HobbyCode (this field may/may not even be needed - I guess you could even carry it further and create another relational "lookup type" table for HobbyCode but I personally wouldn't do it.)
    HobbyName

    And then as possible lookup tables:

    GenderLookupTable-Genders
    -Gender (linked to Gender in CustomerDataTable)
    -GenderName

    CountryLookupTable-CountryCode
    -CountryCode (linked to CountryCode in CustomerDataTable)
    -CountryName
    Last edited by pkstormy; 04-06-08 at 13:18.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Apr 2008
    Posts
    7

    Creating ERD

    Thanks Kindly,my other question is,if i need to show Parent Child relationship with my Tables?.Joining 2 Tables,let say Customer and Hobby is good enough?

    Thanks in Advance,
    P

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    pipilanka2008 - I edited my last post just now (sorry about that) but take a look at the post again.

    Yes - in my opinion (and again, if you have many hobbies for 1 customer), I'd go with the outline I made in the previous post. Then to return ALL the information you would ONLY need to link 2 tables.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If each customer though ONLY had 1 hobby (at least that you were tracking), then I wouldn't make the Hobby table relational.
    Last edited by pkstormy; 04-06-08 at 15:00.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Flat tables are not evil. The thing about normalization and flat tables is to prevent things like the following records in 1 flat table:

    ID = 23, John, Doe, F, 407 W Somewhere St, Madison, WI, Collects Stamps
    ID = 24, John, Doe, F, 407 W Somewhere St, Madison, WI, Collects Coins
    ID = 25, John, Doe, F, 407 W Somewhere St, Madison, WI, Collects Rocks

    (Notice the information which has to be repeated for the 1 field which has different (multiple) information for the same customer.)

    verses 2 tables:
    ID = 23, John, Doe, F, 407 W Somewhere St, Madison, WI

    ID = 23, Collects Stamps
    ID = 23, Collects Coins
    ID = 23, Collects Rocks

    verses a poor design - 5 tables (with everything broken out in separate tables):
    ID = 23, John, Doe, 407 W Somewhere St

    ID = 23, F

    ID = 23, Madison

    ID = 23, WI

    ID = 23, Collects Stamps
    ID = 23, Collects Coins
    ID = 23, Collects Rocks

    (notice the difference and what would be easiest?)
    Last edited by pkstormy; 04-06-08 at 14:57.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Apr 2008
    Posts
    7
    Hi Pkstormy,please dont go away,let me work on solution you just unfolding to me,then i will see how i go.

    Again thank you kindly.
    P

  9. #9
    Join Date
    Apr 2008
    Posts
    7
    HI pkstormy,kindly have a look,i did try to correct my Tables,However i would kindly ask you to AMEND them again.

    Here is my Original FLAT FILE Attributes,with SAMPLE DATA:
    CustomerID–(TEXT)—sample data [346yytr3r]
    LastName-(TEXT)--sample data [Broke]
    FistName-(TEXT) --sample data [John]
    Phone-(NUMBER) --sample data [88888888] NB:at this stage no multiple phone no
    Gender-(TEXT) --sample data [M]
    GenderName-(TEXT) --sample data [Male]
    MemberSkill-(TEXT) --sample data [P2]
    MemberCost-(NUMBER) --sample data [25%]
    CountryCode-(TEXT) --sample data [UK]
    CountryName-(TEXT) --sample data [United Kingdom]
    Hobby-(TEXT) --sample data [Tennis,Footbal,Painting]

    The Table i did try to Create from Original FLAT FILE
    CustomerDataTable:
    CustomerID
    LastName
    FirstName
    Phone
    GenderName
    MemberSkill
    MemberCost
    CountryCode
    CountryName – CAN I LEAVE THIS FIELD or ELIMINATE IT?

    HobbyDataTable
    HobbyID –SHOULD I CREATE THIS ATTRIBUTE,KEEP IN MIND DIDN’T EXIST IN MY ORIGINAL FLAT FILE
    CustomerID
    HobbyCode – SHOULD I CREATE THIS ATTRIBUTE,ALSO DIDN’T EXIST IN MY ORIGINAL FLAT FILE
    HobbyName


    This part I still didn’t understand It!

    When you say GenderLookup Table, which I suppose I should name it Genders ,
    1.HOW/WHERE, DO I CREATE IT AT FIRST PLACE.
    2.WHAT IS LOOKUP TABLE?,Kindly explain
    3.Is LOOKUP TABLE,normal Table,just like other Tables.what is the difference between this Table and the rest of other Tables

    Can you elaborate more here below

    And then as possible lookup tables:

    GenderLookupTable-Genders
    -Gender (linked to Gender in CustomerDataTable)
    -GenderName

    CountryLookupTable-CountryCode
    -CountryCode (linked to CountryCode in CustomerDataTable)
    -CountryName

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You'd have to leave foreign keys in your Customer Table:

    Customer
    -CustomerID
    -LastName
    -FirstName
    -Phone
    -GenderCode
    -MemberSkill
    -CountryCode
    -HobbyCode

    Gender Table-Genders
    -GenderCode
    -GenderName

    Member Table-MemberSkill
    -MemberCosts

    Country Table-CountryCode
    -CountryName

    Hobby Table
    -HobbyCode
    -HobbyName

    Then you could should be able to relate them. Personally I don't see a need for a genders table.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Bah. I answered this in the other topic you created.

    I wish those duplicate topics were linked and locked!!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  12. #12
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    An Example

    Attached is an example I quickly made for you using your situation. If this doesn't help, try looking at the Northwinds database which ships with Access. You need to look at examples and see how they would work in your situation. Also, study posts that talk about relationships and the best ways to create them and when you should/should not create a relational table. Look at examples. Read up in the Access help. Get the Developer's Handbook by Sybex. To thoroughly understand relationships/lookup tables, you need to read and try out different samples. But hopefully the attached example will guide you in the right direction for you to tweak it to fit your needs.

    Before you ask more questions, seriously look at the attached example and the code and thoroughly study how it works. Things will then start to make sense on how the relationships are setup and what a lookup table is used for (i.e. the comboboxes). I also made a data entry form for you so you can see how it works, a report as well so you can see how it would look like, and a query so you can see what it looks like with the tables linked (flat look).

    But it also sounds like StarTrekker has already given you some guidance.
    Attached Files Attached Files
    Last edited by pkstormy; 04-07-08 at 01:31.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  13. #13
    Join Date
    Apr 2008
    Posts
    7
    Hi Star Trekker,
    Thank you kindly,will try and see if Access recognise my ER and it does make sence in the whole RELATIONAL DATABASE my trying to create.I will get back to you shortly.
    P

  14. #14
    Join Date
    Apr 2008
    Posts
    7
    Hi,Pkstormy.
    Thank you i appreciate your input,i got it this time.

    P

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by StarTrekker
    Bah. I answered this in the other topic you created.

    I wish those duplicate topics were linked and locked!!
    Threads mergified.

    pipilanka2008 - please only one thread per question.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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