Database design question?

  • Thread starter Thread starter perspolis
  • Start date Start date
P

perspolis

hi everyone
In my accounting database ,there are several account like Persons,Banks,....
I design them in separated table.
in your opinion ,it's better to have all in one table or separated table??
thx in advance
 
Hi Persopolis,

Are Persons,Banks,..... seperated clients than it is in my opinion one
table.

Are Persons people who are working at a Bank, than it are two tables

Just my thought,

Cor
 
hi everyone
In my accounting database ,there are several account like Persons,Banks,....
I design them in separated table.
in your opinion ,it's better to have all in one table or separated table??
thx in advance
Click to expand...

It is always best to normalise your data, as this allows the most flexibilty
when querying that data.

For a brief Powerpoint slideshow on normalisation, look at this
http://www.soc.staffs.ac.uk/jsf1/courses/apo/lectures/normalisation1.ppt

Let me just quickly demonstrate the benefits of normalisation :

Customer

Account

Bank

If you put all these types into one table then you will be repeating the
columns for Customer in every row that is added for each of the accounts
that they have.

Now imagine that a Customer has two accounts in each of two banks; that
would mean that the Customer information will be recorded four times.

When you alter the Customer's Address which one of the records do you alter?
You would have to alter all four carefully to ensure that no differences
occur.

It is better to separate out the tables so you would get only one record per
Customer, only one record per Account and only one record per Bank.

Now you can design your tables to link to each other using 'foreign keys';
i.e. using a value from one record to link that record to one or more
related records

e.g.

Customer
ID
Name
Address

Bank
ID
Name
Address

Account
ID
CustomerID
BankID
Balance

I can now query the Accounts table to find any Accounts that are for: one
Bank for all Customer, one Customer for all Banks or one Customer at one
Bank.

Look up information on 'normalisation' and 'third normal form' to see the
general rules of good database design.

Joanna
 
hi Cor
no persons and banks are seperated..
but bank has some fields that isn't in Persons and reverse.
then we have some fields that must be null.in ur opinion isn't it bad??
if I separete them ,we don't need null fields.
??
 
thanks Carter
good explanation :)
Joanna Carter (TeamB) said:
It is always best to normalise your data, as this allows the most flexibilty
when querying that data.

For a brief Powerpoint slideshow on normalisation, look at this
http://www.soc.staffs.ac.uk/jsf1/courses/apo/lectures/normalisation1.ppt

Let me just quickly demonstrate the benefits of normalisation :

Customer

Account

Bank

If you put all these types into one table then you will be repeating the
columns for Customer in every row that is added for each of the accounts
that they have.

Now imagine that a Customer has two accounts in each of two banks; that
would mean that the Customer information will be recorded four times.

When you alter the Customer's Address which one of the records do you alter?
You would have to alter all four carefully to ensure that no differences
occur.

It is better to separate out the tables so you would get only one record per
Customer, only one record per Account and only one record per Bank.

Now you can design your tables to link to each other using 'foreign keys';
i.e. using a value from one record to link that record to one or more
related records

e.g.

Customer
ID
Name
Address

Bank
ID
Name
Address

Account
ID
CustomerID
BankID
Balance

I can now query the Accounts table to find any Accounts that are for: one
Bank for all Customer, one Customer for all Banks or one Customer at one
Bank.

Look up information on 'normalisation' and 'third normal form' to see the
general rules of good database design.

Joanna
Click to expand...
 
Persopolis,

Than you would have to make

A table Accounts with all standard information
an extra table Banks related to accountsID (one to one)
an extra table Persons related to accountID (one to one)

I would not do that, when it are just some fields, than you loose that again
with the relations and it makes it more difficult (however that is
pragmatism not conform the rules).

Cor
 
Back
Top