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

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!

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...

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.

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.

Focussing on
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?

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.