I am currently embarking on a new venture to learn PHP and MySQL. I have done some simple databases in the past using Access, but this one is to be a web-centric database for tracking a myriad of data including contacts and project information. I will need to link the various tables in various relationships, and I am not sure the best way to do that. Since I am just starting out with PHP/MySQL I am researching online sources for learning as much as possible. If anyone has recommendations on books or websites, I would appreciate it.
In setting up my tables, one major area that I am concerned with is contacts. I will have a variety of contacts that include: employees, clients, vendors, subcontractors, etc.. and a single contact can be multiple types and each type would have various additional fields that pertain to them. My thought was to have one contacts table that links to other tables for the various contact types. I'm not sure which field type or setup of table options are best... Thoughts?
This scenario will likely play out in other areas of the database as well for projects and products.
Any pointers/direction would be appreciated.
WES
Are you familiar with Object Oriented Design.
In a RDBMS, such as MySQL, I would design the database as follows:
Your tables that extend Contacts would hold their specific data as well as a contact_id column, which creates the relationship.
As an aside, NoSQL solutions solve this problem natively as they don't have a rigid schema. Meaning you could save various data for each record.
I would give a little thought to how you're going to use those extended fields. Certainly create a table for contacts, and you definitely could create a table for each contact type (employees, clients, etc) with a column connecting the record to the contact table (thus employees would have employee_id, contact_id, propertyOne, propertyTwo, etc).
Another option though, which may be convenient if your application is contact centric and you really just want to be able to associate different kinds of information with contacts, would be to have a contact table, a table containing the types of extended information (say "contactTypes" and it would have the information that a vendor type has a billing address for example) and a third table to actually hold all the data (name-value pairs). This is a bit more fluid in that it will let you add new types of contacts or add fields to a type without actually altering your schema. The first option (Jason McCreary's) might scale better if you're going to have many, many records...
Regarding resources - there's so much out there, I can't even begin to narrow it down for you - look at the php manual and just google "php mysql tutorial" - tons of stuff.
A book I would recommend that helped me a lot is Beginning-PHP-MySQL-Novice-Professional
And make sure you watch the following video, but it might be advanced if you do not know the basics. Bbuild-a-login-system-for-a-simple-website/
For database relations as well as an introduction visit Coding HORROR
I hope this helps..