Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2007
    Posts
    12

    Wink could some1 check my improved design?

    hi!

    http://img77.imageshack.us/img77/1042/2345jl8.jpg

    do i think that will work? and does it pass all 1st 2nd 3rd normal form?

    thanks! )

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Are you sure Employement details is a one to one relationship?

    One employee can have one career record?
    If so, why not include it in your employee table?

    But I would think one employee can have more than one career record.
    (left the company and rejoined...)
    ---
    Account > employee relationship: Prove me wrong on this one!

    It looks like you originally had a many to many relationship between employee and account - hence the customer_account table...
    Now, can you explain to me why it was a many to many relationship?
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2007
    Posts
    12
    hi! i want to keep one-to-one between the employee and employement_details otherwise the table gets too big. i dont need to worry about career records and so on. an employee can act as a banker to the customer, so a banker can have many customers, a customer can have 1 customer account, a customer_account can have many accounts. basically the customer_account table acts as an intersection entity.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    I'm unconvinced by your arguements here:
    Quote Originally Posted by player123
    ... otherwise the table gets too big
    Look at your two tables: If you dropped the Employment_Details tab and inserted StartDate and LengthOfEmployment into the Employee table, wouldn't that be LESS data?
    [HINT: the answer in this case is "yes!"]
    Quote Originally Posted by player123
    i dont need to worry about career records and so on
    Why don't you have to worry? Why are they there if you don't have to worry about them?

    As for the many to many relationship jiggery:
    Many customers can have many accounts?

    That implies that:
    - 1 Customer can have many accounts
    - 1 Account can have many customers
    So can customers share accounts?
    George
    Home | Blog

  5. #5
    Join Date
    Mar 2007
    Posts
    12
    oh yeah ur right. i will merge the two tables. i dont know why i havent done that in first place. probably cause the whole of my design is rushed.
    yes there was a many-to-many relationship, but it is problematic, thats why i have done a bridging table between them.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Quote Originally Posted by player123
    yes there was a many-to-many relationship ...
    Was? When creating the ER diagram just ask yourself:
    Does:
    One A have One B,
    One A have many B or
    Many A have many B ?
    George
    Home | Blog

  7. #7
    Join Date
    Mar 2007
    Posts
    12

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    From your diagram:
    1 Branch has many employees
    1 Employee has many customers
    Many customers have many accounts
    --> 1 customer has many accounts AND
    --> Many accounts can have 1 customer

    Tell me - does that seem right to you?
    George
    Home | Blog

  9. #9
    Join Date
    Mar 2007
    Posts
    12
    yes.........

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Quote Originally Posted by player123
    yes.........
    ..fair enough

    wearing my devils advocate cape (very fetching it is to....)

    why would an account holder have a bank employee directly assigned to them
    I would have thought the majority of the cases a person may hold multiple accounts with a branch and that should be the link not the employee.
    Arguably in reality they may hold multiple accounts held at one or more branches.

    they may have somebody allocated specifically to look after them, but on UK practise, unless they are stinking rich its unlikely that many will have a specific person allocated to the account. Mnay business acoutns have a notional business banker.. but in any event the account is run form the business banking centre, not run as part of the employees portfolio

    by implementing it this way you are potentially creating a problem if say an employee moves to a new branch you will have to manually reallocate that employees accounts back to a new employee at the old branch

    theoretically an employee can work at one or more branches (at least they do seem to in the Royal Bank of Scotland, does your real world situation have the same issue, if so can it handle that.

    An account is unlikely to move branch unless its handled by a simple transaction. so to move an account between branchews you need to know who the new person handling the account will be, ratrher than say the old branch merely changing 'their' data the banks IBAN or sort code to the new branch and lkettign the new branch do what ever they want. you modle will force someone to move an account to a new person.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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