Sorry but a) there's more than one way to do this and b) you haven't really shared enough info for me to fully layout the tables that you'll need.
Clearly you need a table for clients and a table for projects. You may need several "detail" tables to document the project details specific to a kind of project or you can just define a lot of fields in your project table and use only those fields that are needed to document a given project.
But will you need to link more than one client to the same project?
That would require a join table between projects and clients in order to link multiple clients to the same project and yet also be able to link multiple projects to the same client. And you may find it useful to define related tables for different contact details such as a related table of phone numbers and email addresses associated with a given contact.
And you might need a company table if you need to document information about the contact's company--especially if you have multiple contacts fro the same company.
Thanks for the quick reply! Each client will have only one contact and fields such as email address etc, basic stuff. Each project type will have different fields, so a Print entry will need "Paper Weight Used", "Print Cost" etc. A Wedding project will have "Wedding Venue" and "Honeymoon Destination" etc. I may have around 10 different project types.
Each client will only have one contact. Each project only one client. I don't want to use only one table if possible for projects as the vast majority of fields will be inappropriate. For a client I do regular design work for I don't want it cluttered with Wedding fields.
All I need to see is: when I select a client name, I can see a list of their previous project titles. Ideally, I'd then be able to click on one and see a more detailed view of it (all the fields associated with it).
I can already do this with just a Contacts table and a Projects table and set up a portal to show projects for that company. What I'm unsure of, is if that company has different project types. If I put all the fields in one big table it would work but be very untidy.
What's the difference between a "client" and a "contact"?
And how many of each might need to be linked to a given Project?
What you describe for Project Types is pretty vague. They might well be handled simply as a group of related "detail" records for a given project--much like an invoice can list multiple items on a single invoice and each invoice can have a very different list of items.
Sorry client and contact are exactly the same. Client will be the company name, contact will be the person who I deal with. As I said, only one per project.
Not sure how to describe projects in more detail. OK, here are two examples:
Print Project fields:
- Project ID; Project Name; Design Charge; Paper type used; Print cost; Quantity produced;
Wedding project fields:
- Bride's name; Groom's name; Wedding venue; Reception venue; Deposit paid; amount remaining; honeymoon destination; wedding date; notes; children's names; engagement shoot needed;
So when I enter a new client, I want to choose the type of project they are associated with, for example a print project. In the future, that same client may come back and order a Web Design Project. Then they might order an Event Photography Project. When I look up that client, I want to be able to see the project names associated with them and then be able to click through to see the other fields of any given project. Does that make it clearer?
Actually, you didn't say only one per project. You said "one client per contact" in your previous comment and that's why I asked for clarification.
When it comes to projects you need a unified table where every project has exactly one record no matter the type. You need that in order to link clients/contacts to projects effectively. As previously stated, whether you need a table for the client and a table for the contact depends on whether you just record the name of the company or also need to record other details about each company.
How you handle the different types of projects is something that presents you with several different design options.
One method is to set up a related table where one field identifies the type of data recorded: Groom's name, Wedding Venue, etc. And the second field in the same record records the value. This is the most flexible approach as you can literally document any event in nearly any way that you need, but it's also the most challenging to you as a developer to make work.
Another method is to set up a different related table for the details with a field for each detail. So in your project table, you have only the fields that are common to every project such as a Project ID, Project Name, Date and a project Type. The fields unique to a particular type are defined in a "detail table" with one such table for each project type. Documenting a given project requires creating one project record and one project detail record. You can set up layouts for each project type that combine the fields of the two tables.
A third method is to have a single project table with a large number of fields--with only a few of those fields used by any one project type. This also can be set up with a different layout for each type of project. This last option can have some efficiency issues when you get large numbers of records in your tables but is also the easiest for someone new to database design to work with.
My recommendation here, assuming that you are new to relational database design, is to select the 2nd or 3rd design option and make a trial run at designing your database. Don't consider your design as fixed in stone, but rather a "rough draft" of your final version and then revise and modify your design as you go along and learn more about what does and does not work for you.
Thanks Phil. i have created 2 tables: one client, one projects. The project table will have tabs, one for each type and I will fill in the appropriate bits. When I see the clients, the portal will display the project titles and I've set up a button to take me to the related record for each project title. The button will also take me to a specific layout based on the project type so a Wedding project type will take me to the Wedding layout which of course only has Wedding fields in it. A second button will then take me back to the client view.
I'm sure there's a more elegant way of doing it but this works for me. Thanks for your help!