Help to normalliase to 3NF 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

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

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

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!!!

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!!!

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!

I think it all looks quite healthy now!

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

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

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...

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

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

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