Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Jan 2004
    Posts
    30

    Help to correct design faults

    http://www.dbforums.com/showthread.p...5&pagenumber=1 This was the orginal post


    OK sorry to bring this issues back up however I am still not having any luck with this design.

    I have put the design into MS access to test it functionality and I have tried to reduce the data redundancy.

    What I find is that say I enter data on user it let me enter account details but there is no link to card even though the tables are linked correctly.

    Now I have 5 tables. All I need to do is to be able to drop this design into pointbase and make a prototype to test it works ok.

    The basic goal is to build a simple backend banking database to the research into supporting a smartcard system. I must be able to create new users and capture data from transactions usage. I must be able to credit and debit the account holder using sales and transfers.

    Please could any add to what already suggested as I am getting bogged down and am not making any head way.

    Forgive if my desription is a bit vague but as you might be able to tell i am reasonably new to all this.


    Thanks
    Sean
    Attached Thumbnails Attached Thumbnails export.jpg  
    Last edited by Sean_W; 03-27-04 at 10:37.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Help to correct design faults

    Originally posted by Sean_W
    What I find is that say I enter data on user it let me enter account details but there is no link to card even though the tables are linked correctly.
    But they are not linked correctly, as far as I can see. You have:

    A User may have many Accounts
    A User may have many Cards

    For example:

    Accounts
    UserID AccountID
    1234 767676
    1234 457093

    Cards
    UserID CardID
    1234 5555
    1234 7777

    It is impossible to tell whether Card 5555 is for Account 767676 or Account 457093, or indeed for some other Account altogether!

    You need a foreign key from Card to Account:

    Cards
    AccountID CardID
    767676 5555
    457093 7777

    (You no longer need the UserID in the Card table because it can be derived via the Account table).

    So the ERD looks more like this:

    User ----< Account ----< Card

  3. #3
    Join Date
    Dec 2003
    Location
    Tokyo Japan
    Posts
    26

    how about this?

    here are my assumptions in the attached diagram

    - the CARD NUMBER is basically the ACCOUNT NUMBER (or you could basically have a different CODING scheme, which in itself should be UNIQUE for each ACCOUNT NUMBER)
    - similary you could have a different CODING scheme for the ACCOUNT NUMBER, which in itself should be UNIQUE
    - each ACCOUNT should have a corresponding TRANSACTION (like withdraw, deposit, transfer)
    - a STATEMENT is basically a COLLECTION of TRANSACTIONS

    ps: in my design, i assume INTEGERs for ALL "id"s, while the CODES could be chars, or varchars
    Attached Thumbnails Attached Thumbnails erd_reply.jpg  
    Last edited by cybershadow_jp; 03-29-04 at 02:58.

  4. #4
    Join Date
    Dec 2003
    Location
    Tokyo Japan
    Posts
    26

    Re: how about this?

    Originally posted by cybershadow_jp
    here are my assumptions in the attached diagram

    - the CARD NUMBER is basically the ACCOUNT NUMBER (or you could basically have a different CODING scheme, which in itself should be UNIQUE for each ACCOUNT NUMBER)
    - similary you could have a different CODING scheme for the ACCOUNT NUMBER, which in itself should be UNIQUE
    - each ACCOUNT should have a corresponding TRANSACTION (like withdraw, deposit, transfer)
    - a STATEMENT is basically a COLLECTION of TRANSACTIONS

    ps: in my design, i assume INTEGERs for ALL "id"s, while the CODES could be chars, or varchars
    my apologies - i just realized a problem with the relationship i have defined for "TRANSACTION - STATEMENTS"

    ill try to upload a better design momentarily

  5. #5
    Join Date
    Dec 2003
    Location
    Tokyo Japan
    Posts
    26

    Re: how about this?

    Originally posted by cybershadow_jp
    my apologies - i just realized a problem with the relationship i have defined for "TRANSACTION - STATEMENTS"

    ill try to upload a better design momentarily
    here is my new design...

    in this case, [optionally] you could have "STATEMENT_HEADER" and "STATEMENT_DETAILS" instead of only "STATEMENT"

    and also please note that the ACCOUNT_ID in the CARD entity could actually be mapped into "CARD_ID" if so preferred, just be sure to mention that "ACCOUNT_ID is equal to CARD_ID"

    ps: to the others, i would really appreciate comments (and even violent reactions) regarding my design... thanks!!!
    Attached Thumbnails Attached Thumbnails erd_reply.jpg  
    Last edited by cybershadow_jp; 03-29-04 at 04:25.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: how about this?

    It appears now that each statement can only relate to a single transaction (via the foreign key) which doesn't sound right.

    Also, you now have a 1:1 relationship between Account and Card. Are you keeping them in separate tables because an Account may not have a Card?

  7. #7
    Join Date
    Dec 2003
    Location
    Tokyo Japan
    Posts
    26

    Re: how about this?

    Originally posted by andrewst
    It appears now that each statement can only relate to a single transaction (via the foreign key) which doesn't sound right.

    Also, you now have a 1:1 relationship between Account and Card. Are you keeping them in separate tables because an Account may not have a Card?
    in regards to your first point - yes, i did realize the same... actually, my design wasnt as detailed as it was supposed to be... im attaching a new version. the STATEMENT table is actually the StatementHeader...

    what do you say?

    in regards to the second point - yes, i am assuming that an ACCOUNT may have not been issued a CARD (like here in Japan, where you could only have a bankbook).

    also, we could also see this that an ACCOUNT is a different animal from a CARD - and this would allow more flexibility when stuff are added to the designs (say, something more "linked" to a CARD instead of an ACCOUNT)... what do you say?

    thanks!!!
    Attached Thumbnails Attached Thumbnails erd_reply.jpg  
    Last edited by cybershadow_jp; 03-29-04 at 05:34.

  8. #8
    Join Date
    Dec 2003
    Location
    Tokyo Japan
    Posts
    26

    thanks

    one thing i like about this group is that there are those who are willing to comment on what you do (violent or otherwise)... this allows me to refine my designs, and allows me to learn new things...

    with me not having a mentor (a senior) from whom i can learn from - this is a very much welcome...

    thanks!

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: thanks

    I think it all looks quite healthy now!

  10. #10
    Join Date
    Jan 2004
    Posts
    30

    Re: thanks

    What can I say apart from thank you very much for all your help and suggestions I have a representation of this model made in MS access so later on I am going to change my old design to match yours and see if it works and will post back later for your thoughts.



    One issue I should have mentioned is that I need to be able to debit and credit the balance after a transaction has accured and show each transactions made against each account represented in one statement.

    I.e. statement has a 1:M relation with transaction.


    Thanks again for all your help, it really is very much appreciated, as you can tell from the original posting date this have been getting the better of me for quite some tine now.


    Sean

  11. #11
    Join Date
    Jan 2004
    Posts
    30

    Test design

    I have built a test database in access following your design would appreciate if you would have a look as it still doesn’t seem quite right.

    I have uploaded the db in MS access as it easier to learn from seeing it physically demostrated.






    Thanks Again

    Sean
    Attached Files Attached Files
    Last edited by Sean_W; 03-29-04 at 14:39.

  12. #12
    Join Date
    Dec 2003
    Location
    Tokyo Japan
    Posts
    26
    checking your DB, here are my comments

    User --> OK

    Account --> OK

    Card --> are you saying that an ACCOUNT can have many different CARDs?

    Transaction --> why do you have the userID, statementID, accountNo, and cardID here? are you trying to create a flat DB (maybe for performance reasons)? all you would need is the accountNo, and through a query you would be able to get the userID, and cardID. the statementID belongs to a different entity, and thus shouldn't belong in the Transaction table. (review the rules of normalization)

    Statement --> similaryly, you dont need the accountNo nor the transactionID here. you can use the StatementDetail for that...

  13. #13
    Join Date
    Jan 2004
    Posts
    30
    Thanks cybershadow_jp

    I am going to make the changes now, basically in this system one accountNo should have one card associated to each accountNo.

    Although in real life I suspect userID should have a 1:M with card. So one user can have O, 1 or more cards associated to each user.


    Have you any idea's of how to implement the balance so I can debit and credit the balance. I believe you need to setup an equation on certain fields.

    Sean

  14. #14
    Join Date
    Jan 2004
    Posts
    30
    Ok I have made the changes you suggested however when trying to enter a transaction there seem to be a link conflict with accoutNo in transaction.

    Could someone please have a look and offer there opinion. Also as mentioned above I need help to get the balance to credit and debit and the data to be accessible from in the database.


    Thanks for all your help

    Sean
    Attached Files Attached Files

  15. #15
    Join Date
    Jan 2004
    Posts
    30
    Ok I have made those changes, but I am thinking that I may need a more entities in order to be able to credit and debit account i.e. though sales and transfers.


    Can anyone with experience offer me any idea's on the best way to achieve a simple implementation of the requirements i have listed.

    Any help would be gratefully received. Please see the outline of the database structure on attachment above.

    Thanks
    Sean

Posting Permissions

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