I just finished watching the Lynda.com training tutorial for PHP and MySQL, for the purpose of creating a database for forms. Can someone please give me advice on any additional fields i should include in my lead database. autoID firstName lastName company address city state zip phone email date
Some fields for you might be good. Just use default values for now. Then you can work on pulling out the data from the db so like its a real program. lastcontact subscriber_status (for newsletter) notes
Are you planning to normalize the table? Just asking because I see both name and address data in the same table, if you denormalized please ignore message. Also, the current structure allows 1 and only 1 address per lead. How will you address multiple leads for the same company without re-typing the company information or updates?
Database normalization is a design technique. The principles address possible duplication of data, data integrity, and can extend into other areas. For example: If I have 2 leads from the same company the table with the current design will appear as follows: autoid firstname lastname company address city state zip ... etc. ------ --------- --------- -------- -------- ---- ----- --- 1 John Smith Walmart 10 Main BT AR 72716 2 Joe Smith Wal-Mart 10 main Bt Ar 72716 Note that there is duplication in the company and address columns, furthermore you have data inconsistency issues too. Regarding other fields, can a lead have multiple addresses? Possibly and if so, the database should be able to handle this relationship type. I hope the above makes sense. Defining database normalization and design is out of scope here, but I can help as needed.
I will try to take this to 3rd normal form for the purpose of a form, please criticise my effort. clients client_ID (auto increment) firstName lastName address state zip telephone date_1st_contact (DATE) *employee_ID *company client_email --------- companies company - PK address city state company_tel company_email -- employees employee_ID - PK employee_details *client_ID
CREATE TABLE Clients (client_ID NUM, first_name CHAR, last_name CHAR, address VARCHAR(50), state CHAR, zip VARCHAR(20), telephone NUM (15), date_1st_contact (DATE), *employee_ID NUM, *company VARCHAR, client_email VARCHAR):
I do not know the business requirements, so any and all suggestions are based on assumptions. I would move the address out of the clients table to provide a 1-to-many relationship, same for phone (i.e. home phone, cell phone, fax phone, etc), and do the same for the company table. There are many options, this is just one and the one to choose is the one that meets the requirements.