Results 1 to 9 of 9
  1. #1
    bjornas is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2015
    Posts
    17

    Simple RDBS design question

    Hello, I strive to fully grasp the concept of RDBS design while trying to teach myself the concept while designing a database to keep track on payments related to a school excursion.



    I have a table with the surname, given name and address for each participant (there are more fields, but they dont matter here). The table has an ID autonumber field.

    I also have a table with payment details: Date received, amount received and name of sender (as the sender is not necessarily the same as the participant). That table also has an ID autonumber field.

    Each participant typically pays in installments, so that one can expect several payments for each participant, perhaps with different amounts and senders.

    I try to relate those tables by dragging a join line from the ID field in each table. However, that creates a one to one relationship, which probably is wrong.

    How shall I create a meaningful relationship between those tables? Which field should be related and in which "direction"?

    Sorry for my poor explanation. Any suggestion is deeply appreciated.


    Rgds, Bjørn Olav, Oslo, Norway.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I also have a table with payment details: Date received, amount received and name of sender (as the sender is not necessarily the same as the participant). That table also has an ID autonumber field.
    you need another field called say participantFK - that is the field you would join to your participantID field in the other table. (recommend you change participantID to participantPK).

    PK=Primary Key
    FK=Foreign Key

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Bjørn
    Can you show us your current tables and relationships (relationships window) with tables expanded to show all fields?

    You may find this link on Normal forms helpful or overkill, but it does deal with design.

  4. #4
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Bjørn Olav

    Your 2 table structures should be something like this:-

    tblPersons
    -PersonId - PK Autonumber
    -PersonFirstname
    -PersonLastname

    tblPayments
    -PaymentID - PK - Autonumber
    -PersonID - FK - Number - LongInteger
    -PaymentDate
    -AmountPaid
    -Sender

    You then just Join the 2 tables on PersonID from Both tables and Enforce Referential integrity


    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Bjørn

    It seems to me (who isn't familiar with your details) that there would be a table for excursion/trip.
    Since a student/participant could take multiple excursions during a repayment period, you may want to keep track of excursions,sum of related excursion costs, sum of repayments on behalf of that student's "indebtedness", and be able to calculate his/her balance at anytime.

  6. #6
    bjornas is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2015
    Posts
    17
    Hello all,

    I was able to fix this myself by removing the index from the ID field in the "Payment details" table. The index forced the relationship to be "one to one", and by that, the database did not fill its purpose.

    Therefore, I dont think I need extra "Private key" and "Foreign key" fields as you suggest, Ajax.

    The relationships are as shown in the pic below (Påmeldte = "Participants"; "Betalingsdetaljer" = "Payment details":

    I only need to keep track on one excursion, so I there is no need to make this overly complicated. I just need to teach myself the concept of relationships.

    Anyway, thank you all for your kind assistance!

    Click image for larger version. 

Name:	DBS relationship.PNG 
Views:	18 
Size:	5.5 KB 
ID:	47397

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I just need to teach myself the concept of relationships.
    Don't think you are succeeding

    Removing an index to 'make it work' is like removing the safety protection from a machine so you can fix it whilst it is working.

    If you don't index, your db will run slow once you have a reasonable number of records. see this link https://www.access-programmers.co.uk/forums/threads/why-indexing-is-important-for-good-performance.291268/

    Indexed or not, if you don't uniquely identify a record (the purpose of a PK) you will struggle when you need to identify a specific record

    Good luck with your project

  8. #8
    bjornas is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2015
    Posts
    17
    Ooops... What about this design, then:

    Click image for larger version. 

Name:	Skjermbilde.PNG 
Views:	18 
Size:	5.8 KB 
ID:	47398

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I think you should research Primary Key, Foreign Key and relationships. Ajax and mike also provided advice on how keys are used to relate tables. I provided a link on same concepts in an earlier post, but it doesn't appear that you have looked at or understood based on your diagram in #7 and your statement
    I dont think I need extra "Private key" and "Foreign key" fields as you suggest, Ajax
    You are missing the importance of PK/FK.

    UPDATE:

    Seems our posts were done at same time. Just saw your latest post. Your latest diagram shows the proper relationship between Participants and Payments.

    Good stuff!!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 03-15-2020, 01:46 AM
  2. Simple(?) Database Design Question
    By mkltmsck in forum Database Design
    Replies: 4
    Last Post: 07-07-2014, 02:00 PM
  3. Simple Design Question
    By coolpwr in forum Access
    Replies: 4
    Last Post: 04-18-2014, 09:21 AM
  4. design Simple Attendance System
    By AzizSader in forum Forms
    Replies: 2
    Last Post: 12-07-2013, 11:26 PM
  5. Database design for simple inventory
    By toptech in forum Database Design
    Replies: 12
    Last Post: 10-24-2009, 07:24 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums