Hi All

I am quite new to database design please go easy with me. I am looking for some advice as the best way to structure some data to 3NF.

I am correct trying to develop a project for my course at Uni. The project is based around a smartcard epurse. I am creating an application that when a user purchases a product though a merchants web site after the transaction is authorized the account is debited by the bank which also remotely debit the balance on javaccard of the client.

I want to design a database that can be used to store data using sun one 5 enterprise java beans with a pointbase database server. As I am using Sunone IDE that actual implemenation isn't that bad due EJB wizards integrated into the IDE.

I would just like to get the opinions of someone who has a bit my experience than myself which shouldn't be too hard due to the fact I am new to databases.

Here is the table and fields I have selected so, any suggestions would really be welcomed. The main problem I have is how to make sure that do not replicate the data unnecessary.






CardHolder
unq_key
first_name
surname
post_code
city
county
telephone


Account
unq_id
accountNo
sort_code
first_name
surname
card_issued
date_created
overdraft_amount
balance
card_blocked
card_unblocked



Card
card_id
certicate
cardholder_firstname
cardholder_surname
accountNo
date_expired
date_valid

Statement
statement_id
accountNo
date_purchased
amount
location
current_balance

Merchants
mer_id
accountNo
merchant_name
certifcate
terminal_id

Transaction
transaction_id
card_id
Card_accountNo
merchants_accountNo
merchants_id
Terminal_id
cardholder_name
authorisation_code
delcined




Any advice especially with normalising the data would be very much apreaciated.

Sean

First look at transaction. You have alot of redundant data in there.

If you know the cardID and the MerchantID you could find much of the information in the Card and Member tables.

Let's backtrack completely. First you have to identify the entities and the relationships.

Take the names of the entities and go:

Relationship One:
A cardholder has (zero, one, many) Accounts
An Account has (zero, one, many) cardholders

Go through all the entities and identify all the relationships and the cardinality as above.

Post all the relationships here and I will tell you the next step. There will be three steps.

Ok let me take the time to thank you certus for taking the time to offer assitance it very much appreactied so thanks a lot.

here what i have come up, so far.

see attachment for first go at ER Diagram. I did try and make CardHolder primary key join transaction table but the tool Case studio 2.1 wouldn't let me so i presume it wasn't valid.


Here are the table now

CardHolder[
Account
Card
Statement
Merchants
Transaction
card_id : PK
accountNo: FK
first_name
surname
post_code
city
county
telephone
}

Account{
Card_id :PK
accountNo: FK
sort_code
first_name
surname
card_issued
date_created
overdraft_amount
balance
card_blocked
card_unblocked
}


Card {
card_id: FK
accountNo: FK
certicate
firstname
surname
date_expired
date_valid
status

}


Starement{
transaction_id: PK
accountNo :FK
statement_id
date_purchased
amount
location
current_balance
}


Merchants{
merchants_id: PK
accountNo
merchant_name
certificate
terminal_id

}

Transaction{
accountNo: FK
card_id: FK
merchants_id: FK
Transaction_id
Terminal_id
authorisation_code
delcined
amount
date
time
date_valid
expires

}

And the relation are as follows

Relationship One:
A cardholder has (one, many) Accounts
A Accounts has (Manu, one) cardholder {Not sure whether should be 1:1}



Relationship Two:
Card has (many , one) Cardholder
Cardholder has (one many) Card

Relationship Three:
Account has a (one many) Statement
Statement has a (one many) Account

Relationship Four:
Statement has a (one many) Transaction
Transaction has a (many one) Statement


Relationship Five:
Cardholder has a (one many) Transaction
Transaction has a (many one) Cardholder {I couldn't see to make it work}


Transaction Six
Transaction has a (Many one) Merchants
Merchants has a (one Many) Transaction


Please can make use reply as easy to understand as possible.

Many Thanks to all

Sean

Alright, you have a diagram, good.

First, you have to get your relationships between the entities right.
I'm also going to simplify the language a bit to focus on the relationships and not the detailed cardinality.

Relationship One:
A cardholder has many Accounts
An Account has many cardholders
- sounds like a (M:N) likely one cardholder to many accounts.

Relationship Two:
Card has one Cardholder
Cardholder has many Cards
- one cardholder to many cards

Relationship Three:
Account has many Statements
Statement has one Account
- one account to many statements

Relationship Four:
Statement has many Transactions
Transaction has one Statement
- one statement to many transactions

Relationship Five:
Cardholder has many Transactions
Transaction has one Cardholder
- one cardholder to many transactions

Relationship Six:
Transaction has one Merchant
Merchants has many Transactions
- one merchant to many transactions

Your are attempting to do too much at once with your diagram. First just draw the entity boxes without any keys or attributes inside and connect them with the relationships as are listed above.
Post that diagram. It will be your entity relationship diagram.

Ok here we go, i hope this is ok.
See attachment.

Thanks again for your help,

Sean

Great. Now put in the primary key attributes for each table followed by the foreign key attributes.

Make sure you know what is your natural key for each table first. This will be important in preventing duplicate records.

I have just had ago this, only thing as i am Case studio it gets putting 2 instances of postcode in Transaction.

Blue = FK
Red = PK


What do ya think, i am hoping that this is nearly there.



Thanks
Sean

[QUOTE]Originally posted by Sean_W
I have just had ago this, only thing as i am Case studio it gets putting 2 instances of postcode in Transaction.

Blue = FK
Red = PK


What do ya think, i am hoping that this is nearly there.



Thanks
Sean [/QUOTE


Hmmm i, not sure about postcode for a PK for Cardholder what do you reckon ?

Sean

Post code does seem sort of strange. Determine what else can be used as a natural primary key. Possibly FirstName, LastName and Postal Code. Even that is iffy. That would be your natural key and you could use a surrogate key of CardHolderID.

You are defining your foreign keys as Primary Foreign Keys (PFK). It is necessary in Account to have the CardHolder as a primary foreign key? Is is necessary for Card to have CardHolder as a primary foreign key? Why not just foreign keys?

That would clear up the collision in Transaction of post_code.

Also, what makes a Statement unique? Account and Location? What makes a Transaction unique? Statement, Date, Time and Cardholder?

Get your natural keys and follow with surrogate keys and the rest is gravy.

Right here we go,Im not sure whether the tool is making harder than it needs be. Transaction seem to be overloaded with PFK, not sure whether this would be effective.







Thanks
Sean

damm just noticed cardholder_id is duplicated in transaction.

back to the drawing board



Sean

Sean,

Figure out how to turn your PFKs into FKs in Account, Statement and Card. FKs do not carry over into the next relationship like PFKs.

When you get that worked out you can add your non-key attributes to your entities. And we will take a final look at it. You are just trying to achieve a normalized model, right?

Wish we could sit down at a table with the diagram an a couple of pens.

Same here ,my goal is just a efficient database design without data redundancy, ideally 3NF would be great.

Let me try that out again


Cheers

Sean