Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2007
    Location
    India
    Posts
    16

    Unhappy Unanswered: Database Design - Access 2003

    Could some one tell me if Iam designing the database correctly!The tables to watch for are counselMaster & BranchMaster. In both BankID, zoneID, RegionID are being repeated. Is it possible to break them up.

    The scenario is like this...
    There are more than one banks. Each Bank can have more than one zone (same or diff). Similarly each zone can have 1 or more regions (again same or diff). Each bank would have to query about their own data.

    This for stand alone system.
    The updates would come from branches/regions for HO to compile them (Iam still to work out that - Any suggestions on this would also be welcome )

    Db: Access 2003
    I would be handling all manipulation thru vb6

    Attached Files Attached Files

    Database: MS Access 2003

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if its a review you are after, why not talk to your colleagues or tutor first?

  3. #3
    Join Date
    Dec 2007
    Location
    India
    Posts
    16
    boy! Iam trying to make a db in access and asking for expert guidance (so that I don't have to painfully redesign the db lateron).
    By the way where does the tutor come in?

    Anyway thanks for quick response

    Database: MS Access 2003

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by HACK
    boy! Iam trying to make a db in access and asking for expert guidance (so that I don't have to painfully redesign the db lateron).
    By the way where does the tutor come in?

    Anyway thanks for quick response
    sorry its homework season on the forums, and I mistook your request as a homework, which we don't do, we may help but we dont do

    first thing Id suggest you do is define primary keys, and then your relationships, personally I find it easy if your relationships in the diagram flow from right to left, and / or top to bottom.. just my bugbear but I find it easier to understand.

    If you are modelling a bank then I'd expect the IBAN number to uniquely identify the bank and branch and accoutn... abtually its a composite which could be used to supply all three elements. however its probable that IBAN is not (yet) implmented in your banks.

    there some oddities in the data model that confuse me.. for example you have married status as a long integer.. that column is way to big. I'd actually expect that to be a foreign key to a table defining the marital status (single, married, divorced, cohabiting, civil partnership)

    one thing to bear in mind is that you don't have to use an autonumber column as the primary key if there are what are coilled batural keys out there. so for example if you were in the states, then a suitable priamry key for a stae within the US woudl be the 2 digit state abbrieviation..... that may apply to you region I don't know, much to my shortcomings I don't know much about Indian geography or geopolitics

    I don't understand your naming conention of tables eg ????master, but is your db, your choice

    your consel master is I think suspect, the very fact that you are defiign mulitple addresses and phone numbers suggest to me that you should push the addresses & contact details to a sub table, and classify then using another table say contact type

    can one counsel only have on ecase alloted at anyone time
    do you need to know what cases a specific lawyer has dealt with over time. if so it may need an intersection tables betweeen counsel and cases (probably identifying when that case opened, and that case closed, or if you need to allow multiple lawyers per case identify when a specific lawyer got or released that case

    HTH

  5. #5
    Join Date
    Dec 2007
    Location
    India
    Posts
    16

    Red face

    there some oddities in the data model that confuse me.. for example you have married status as a long integer.. that column is way to big. I'd actually expect that to be a foreign key to a table defining the marital status (single, married, divorced, cohabiting, civil partnership)
    Point taken changing the Field. :thumb:

    your consel master is I think suspect, the very fact that you are defiign mulitple addresses and phone numbers suggest to me that you should push the addresses & contact details to a sub table, and classify then using another table say contact type
    could you explain a lil bit more..
    can one counsel only have on ecase alloted at anyone time
    No one counsel can have more than one case
    do you need to know what cases a specific lawyer has dealt with over time.
    definetly yes

    if so it may need an intersection tables betweeen counsel and cases (probably identifying when that case opened, and that case closed, or if you need to allow multiple lawyers per case identify when a specific lawyer got or released that case
    could you suggest the fields structure

    I don't understand your naming conention of tables eg ????master, but is your db, your choice
    could you suggest how they should be professionally named. you know iam a kid in db so Iam always open for suggestions.

    Database: MS Access 2003

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if youneed to know what cases a consel has had over time, thenyou need to have whats called an intersection table, I tenbd to call em XREF (cross reference) tables

    essentailly it has the primary keys from both the counsel and case.
    eg
    table:CounselsCases
    CounselID FK to counsel table
    CaseID: fk: to case table

    the current way you are modelling this means that you can only have one case per lawyer at anyone time, and can only know the current case a lawyer is assigned. if you never ever switch lawyers between cases then arguably you could store the lawyer ID as part of the case and do away with the XREF table. it comes down to the business requirement... is it possible that multiple lawyers could be on the same case (either concurrently or consecutively)

    your addresses. you have what looks like a home and office address.. what happens if they have say several offices, or several phones, or email addresses...

    a ciandidate for ditching the autonumber would be the sanction authority, where you have a series of codes, effectively which are meaningless to those noting the know.... I'd suggest your sanctioning authority becomes say a 10 digit text field and use that as the PK rather than the autonumber field, I'd add a description of what the sanctioning authority code is.. the reason when you look at your data its immediately obvious what the sanctioning authority is.. it saves having to do a table join to retrieve the code. In olden times disk space was at a premium and doing those sort of tricks would be frowned on, in some places it still is.

    BUT you do need to define your realtionships, define them thas RI, its up to yuou whether you enable update cascade (probably), and delete cascade (possibly but only on data elements, not masterfileds data.. deleting say a sactioning authority code with delete cascade set will take out all cases with that authority code.


    one thing you may care to think about is waht happens if codes such as sanctioning authority are phased out. you may care to have a flag indicating if the code is still valid and can still be used. that way round you allow for old codes and old data to remain in the system, yet not allow new cases to be started with that code.

  7. #7
    Join Date
    Dec 2007
    Location
    India
    Posts
    16
    is it possible that multiple lawyers could be on the same case (either concurrently or consecutively)
    such a thing didn't cross my mind. But its very much possible. So suggest me on this lines.. There could be change of lawyers but not simultaneously. They could be changed at diff stages.

    regarding tracking of cases with each counsel, I was thinking of adding the counselID (FK) in a cases table
    your addresses. you have what looks like a home and office address.. what happens if they have say several offices, or several phones, or email addresses...
    Iam not sure of the structure. Such a thought did cross me, but absence of knowledge how to do it, I restricted to only 2 phones?!
    I'd suggest your sanctioning authority becomes say a 10 digit text field and use that as the PK rather than the autonumber field
    wouldn't that pass on the headache on programmer programatically/user to manually add the new codes?
    Last edited by HACK; 03-18-08 at 09:21.

    Database: MS Access 2003

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by HACK
    regarding tracking of cases with each counsel, I was thinking of adding the counselID (FK) in a cases table
    in which case you don't need the caseid in counsel.That model will support history of whaich lawyer worked which cases over time.. but it wont support multiple lawyers dealing with one case, it wont support a change of lawyer (either replacment from the same firm, or a new firm).


    Quote Originally Posted by HACK
    Iam not sure of the structure. Such a thought did cross me, but absence of knowledge how to do it, I restricted to only 2 phones?!
    you already have that problem.. say if a lawyer has two business phones, more than one home phone, and several internet phones such as skype. they may even have multiple cell / mobile phones
    Im not aware of that many people who have say more than two office address.. but two is certainly possible.. again it all comes down to what you are trying to replicate. Its perfectly feasible that you may have say a lawyer, with several colleagues, each with their own phone number.. it may be that the dealings are with the minion colleagues rather than the lawyer.

    Quote Originally Posted by HACK
    wouldn't that pass on the headache on programmer programatically/user to manually add the new codes?
    no you retain the table, use a code and a description, but use the code rather than the number as the primary key, and refer to the code in the case table


    I think there should be enough there to go on

    I think you are correct to concentrate on the design of the tables before doing any forms or reports....
    but as you have some data try and visualise how it all comes together, how an entity is represented.
    I think it will help you move to the next level to define the primary keys (the one thing that makes that row / record unique.. it can be multiple columns/fields but it must uniquely identify that element
    having done that I think you need to define the relationships, how element X in table Y is related to element B in table A

    one of the problems with the exercise you are doing is that only you understand the minutiae of what is required..... and ultimately its your system, you need to understand the components and how they inter react.
    Last edited by healdem; 03-18-08 at 09:39.

  9. #9
    Join Date
    Dec 2007
    Location
    India
    Posts
    16
    you already have that problem.. say if a lawyer has two business phones, more than one home phone, and several internet phones such as skype. they may even have multiple cell / mobile phones
    Im not aware of that many people who have say more than two office address.. but two is certainly possible.. again it all comes down to what you are trying to replicate. Its perfectly feasible that you may have say a lawyer, with several colleagues, each with their own phone number.. it may be that the dealings are with the minion colleagues rather than the lawyer.
    Point taken but Iam not able to set/decide fields for the New table and linkng to the counsel table. Help plzzz

    Database: MS Access 2003

Posting Permissions

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