Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2007
    Posts
    4

    Noob Design Questions

    Hello all! I am new to the db game and have been working with a contact list in excel that has far outgrown what excel can handle so here it goes.

    Without getting into specifics so I don't lose my job, I need to create a database that includes the following. lets call them Law Firms, Contacts at Law Firms, Disciplines of each Law Firm, A contact log, Weather or not we have worked with the firm, and if we have worked with the firm was it directly or as a sub contractor of another company that was employed by the firm.

    Here are the tables I think I need, (this is where you can set me straight.)

    Firms (FirmID, Firm Name, Contact Info in multiple fields)
    Contacts (ContactID, FirmID, Contact Into in multiple fields)

    Is that O.K. so far?

    Where I get stuck is in the disciplines part. should I include fields for each discipline in the Firms table with a simple true false value for each discipline and work history? Or do I need to create a separate table for that info?

    Also the contact Log I believe should be simple

    A (FirmID, ContactID, Type of contact, contact notes)

    Now being that I am new to the game I realize that I may be way off. Fire away and thanks for the help!

    -Chris

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    I always find the best way to realising any database down is to take the whole thing right back to basics...

    One Firm can have many Contacts
    One Contact can have ...

    When you've come up with this - let us know



    EDIT: By the by... This is not a n00b question - it's just something you need help with!
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    not knowing the legal world....
    Id expect a table to store a company's common details, including say the main switch, website etc.
    a sub table to that to identify any branches (including ther main switch / collection email account for that branch)

    Id then expect some form of table to enable you to identify which legal parastite did what style of work, eg group A converyancing, B Corporate Law, C Insolvency, D Family Court

    Id then expect a table for contacts associated with the firm, probably hanging off the branch table.. so you can identify who works in what branch, that should identify what their role is

    for each person Id expect their persoanl contact details (eg direct line, PA, email etc...), I'd expect the generic switch departmental email catchall to be at branch level.

    Id expect the details of contacts to be associate to both the branch and the legal scum who did the work for you. one potential issue you may have is designing a suitable mechanism to handle when the lawyer moves on (elsewhere within the firm or just elsewhere.. ideally you need to know that a piece or work was down by Ivor Fleecedem or Sue Grabbit & Runne).

    So Id be temtpted to make sure the RI key oin that is not a delete or change cascade.. I'd also be tempted to actually store the legal parasite who did the work as part of that contacts string.

    oops.. am I showing my cynicism of the that most upstanding profession...... the law.. almost certainly, but I have strong reason to do so...
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Aug 2007
    Posts
    4
    ONE Firm can have MANY Contacts
    ONE Firm can have MANY Disciplines
    ONE Firm can have MANY Projects


    ONE Contact can have MANY Logs
    ONE Contact can have MANY Projects


    ONE Project has ONE Project Manager (from our firm)
    ONE Project can have MANY Employees (from our firm)
    ONE Project can have MANY Logs
    ONE Project can have MANY Contacts

    Other information I was not quite sure how to list

    Has a firm worked with us before? Yes or No (Obviously if they have a project with us they have, but I am not quite sure how to illustrate that or if this is even a design issue or not.)

    Have we marketed to the firm and what have we sent

    Maybe something like..

    ONE campaign can be sent to MANY Firms
    ONE campaign can be sent to MANY Contacts
    ONE campaign can be added to MANY Logs????

    The attached Gif is what I have done so far.
    Attached Thumbnails Attached Thumbnails database.gif  

  5. #5
    Join Date
    Aug 2007
    Posts
    4
    Quote Originally Posted by healdem
    not knowing the legal world....
    Id expect a table to store a company's common details, including say the main switch, website etc.
    a sub table to that to identify any branches (including ther main switch / collection email account for that branch)

    Id then expect some form of table to enable you to identify which legal parastite did what style of work, eg group A converyancing, B Corporate Law, C Insolvency, D Family Court

    Id then expect a table for contacts associated with the firm, probably hanging off the branch table.. so you can identify who works in what branch, that should identify what their role is

    for each person Id expect their persoanl contact details (eg direct line, PA, email etc...), I'd expect the generic switch departmental email catchall to be at branch level.

    Id expect the details of contacts to be associate to both the branch and the legal scum who did the work for you. one potential issue you may have is designing a suitable mechanism to handle when the lawyer moves on (elsewhere within the firm or just elsewhere.. ideally you need to know that a piece or work was down by Ivor Fleecedem or Sue Grabbit & Runne).

    So Id be temtpted to make sure the RI key oin that is not a delete or change cascade.. I'd also be tempted to actually store the legal parasite who did the work as part of that contacts string.

    oops.. am I showing my cynicism of the that most upstanding profession...... the law.. almost certainly, but I have strong reason to do so...
    I was using lawyers as an example for your sake let's pretend I said arhitects.

    Thanks for the reply, the theory works equally well.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Focussing on
    Quote Originally Posted by Christopherdb
    ONE Firm can have MANY Contacts
    ONE Firm can have MANY Disciplines
    ONE Firm can have MANY Projects

    ONE Contact can have MANY Logs
    ONE Contact can have MANY Projects

    ONE Project has ONE Project Manager (from our firm)
    ONE Project can have MANY Employees (from our firm)
    ONE Project can have MANY Logs
    ONE Project can have MANY Contacts
    Can you now come up with an ERD? I've just doodled one on paper and it fell together with these statements nicely
    EDIT: Forgot to mention - the highlighted rows need clarifying / rethinking.
    Is it a M:M relationship?
    George
    Home | Blog

  7. #7
    Join Date
    Aug 2007
    Posts
    4
    I have never put an ERD together before, so I'm not sure where to start.

    However, I think that the problem with the lines in red would be that they are basically the same thing correct.

    I don't think they would classify as a many to many relationship becasue while many projects can have many contacts, the nature of the business each project would only have one set of contacts within the same firm, so each project is really its own entity. So therefore One project can have Many contacts.

    Each contact would only be commenting or communicating with one project at a time.

    Am I settimg myself up for failure with this thinking?

    Additionally I am thinking that I am simply going to add a field for each discipline to the Firms table and have a simple true/false statement. My thinking for this is that while many firms could have many disciplines, I really only care about one firm's disciplines at a time.

    Unless of course you can talk me out of that logic.

Posting Permissions

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