Hello, I am a total newbie to MySQL, but have a little bit of SQL knowledge (emphasis on little), so if this is off the wall I apologize in advance.

Long story short: I have data in two tables, one with a prospect's name, address etc, and the other with their phone number(s). This was done in order to enable a one-to-many relationship between the prospect and their phone numbers.

Trouble is, I want to include all this information in a view, so that I can have it all when I bring up a client's record (I call 100+ clients in a typical day). However, with the one-to-many relationship, these views aren't updateable, so I can't write in comments, etc when I bring up the records.

I'd like to write a stored procedure that can take the data from the two tables, and merge it into another table. (I know this breaks every rule in the DB design book, but the database will stay very small, so I'm much more worried about being able to update through the views than I am about performance). I have 15 fields set for phone numbers in the new table, but the trouble is, I never know how many phone numbers will be associated to a particular client. Therefore doing a typical "insert" statement won't work. Any ideas or help will be very much appreciated! Thank you!

My table structure is:
CREATE TABLE `expireds` (
`ID` int(11) NOT NULL auto_increment,
`First_Owner_First_Name` varchar(20) default NULL,
`First_Owner_Last_Name` varchar(20) default NULL,
`Second_Owner_First_Name` varchar(20) default NULL,
`Second_Owner_Last_Name` varchar(20) default NULL,
`MLS#` varchar(10) default NULL,
`Status` varchar(10) default NULL,
`Old_Status` varchar(10) default NULL,
`OMD` varchar(10) default NULL,
`Site_Address` varchar(256) default NULL,
`Site_Unit` varchar(10) default NULL,
`Site_UnitType` varchar(256) default NULL,
`Site_City` varchar(40) default NULL,
`Site_State` varchar(2) default NULL,
`Site_Zip` varchar(6) default NULL,
`Site_Zip4` varchar(4) default NULL,
`Mail_Address` varchar(256) default NULL,
`Mail_Unit` varchar(10) default NULL,
`Mail_UnitType` varchar(256) default NULL,
`Mail_City` varchar(40) default NULL,
`Mail_State` varchar(2) default NULL,
`Mail_Zip` varchar(6) default NULL,
`Parcel_Number` varchar(40) default NULL,
`State_Code` varchar(2) default NULL,
`Subdivision` varchar(256) default NULL,
`Transfer_Date` date default NULL,
`Transfer_Value` int(11) default NULL,
`First_Loan` int(11) default NULL,
`Loan_Type` varchar(1) default NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

With the following table keyed off it:
CREATE TABLE `expireds_phone` (
`user_id` int(11) NOT NULL,
`phone_number` varchar(20) NOT NULL,
`DNC` tinyint(1) NOT NULL,
UNIQUE KEY `user_id` (`user_id`,`phone_number`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And I'm looking to move the data into (different database, prod vs test db in which the preceding tables are located):

CREATE TABLE `expireds` (
`ID` int(11) NOT NULL auto_increment,
`First_Owner_First_Name` varchar(20) default NULL,
`First_Owner_Last_Name` varchar(20) default NULL,
`Second_Owner_First_Name` varchar(20) default NULL,
`Second_Owner_Last_Name` varchar(20) default NULL,
`MLS#` varchar(10) default NULL,
`Status` varchar(10) default NULL,
`OMD` date default NULL,
`FollowUpDate` date default NULL,
`date_last_checked` date default NULL,
`Site_Address` varchar(30) default NULL,
`Site_Unit` varchar(10) default NULL,
`Site_City` varchar(20) default NULL,
`Site_State` varchar(2) default NULL,
`Site_Zip` varchar(6) default NULL,
`Mail_Address` varchar(30) default NULL,
`Mail_Unit` varchar(10) default NULL,
`Mail_City` varchar(20) default NULL,
`Mail_State` varchar(2) default NULL,
`Mail_Zip` varchar(6) default NULL,
`Parcel_Number` varchar(40) default NULL,
`Comments` varchar(9999) default NULL,
`Met` tinyint(1) default '0',
`MetDate` date default NULL,
`Phone1` varchar(15) default NULL,
`Phone2` varchar(15) default NULL,
`Phone3` varchar(15) default NULL,
`Phone4` varchar(15) default NULL,
`Phone5` varchar(15) default NULL,
`Phone6` varchar(15) default NULL,
`Phone7` varchar(15) default NULL,
`Phone8` varchar(15) default NULL,
`Phone9` varchar(15) default NULL,
`Phone10` varchar(15) default NULL,
`Phone11` varchar(15) default NULL,
`Phone12` varchar(15) default NULL,
`Phone13` varchar(15) default NULL,
`Phone14` varchar(15) default NULL,
`Phone15` varchar(15) default NULL,
`DNC` tinyint(1) default NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `Site_Address` (`Site_Address`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Again, I know this design is cringe-worthy, but the database will stay very small, and I need to update comments, etc on the fly so I the next time I call a client, I'll remember what's been said in the past. Because of this, I'm much more worried about being able to update through the views than I am about performance.

Again, I UNDERSTAND MY TABLES AREN'T NORMALIZED, so please spare me the hate mail. I'm looking for a procedure that actually works (as opposed to vague advice I've gotten on other boards), or for a way to update the view with the one-to-many relationship. Will gladly pay for assistance; a good DBA could probably knock this out in 5 minutes. Please respond with your e-mail if you're interested, and I'll get in contact with you.

Thanks again for any and all assistance!

whats wrong with your current design?
..it all depends on how you re maintaining the db, what front end you are suing

I suspect you need another table to hold comments (with say a date+time and whatever else you need)

'all'# you need do is modify the procfess which displays the data and allows edits. this modification needs to insert a row into the comments / contacts table adding the timestamp and so on.

To summarise what you are looking to do is to call up a client. The client should return both the client details and a list of contact details i.e. phone numbers. The number of phone numbers may be none, one or more.

On the form you have the option of adding, deleting or updating the clients details i.e. name, address as well as their phone numbers.

You would like to merge all of this information into a separate table containing all the information. When an update is performed you would like that this data gets added back in to the main application tables. Is this correct?

This could be done easily in a stored procedure but as you say yourself what if you have more than 15 contact details? How will you handle this?

What programming language are you using? Is this PHP? It would seem a better solution is to generate the form more dynamically i.e. populate the client details in the header and then list off all the contact details. You would have two forms, one for the main contact information i.e. name, address etc. and another form for the phone numbers. If you keep a hidden field linking expireds.ID to the phone numbers then you should be able to handle this. This way you are not constrained by the number of phone numbers.

Ronan,

Thank you very much for the reply. I am not using PHP, etc, I'm simply using SQL Manager for MYSQL (EMS SQL Manager - EMS SQL Manager for MySQL: MySQL Database Tool, MySQL Management and Development, MySQL Server Administration, Export and Import Tools for MySQL).

Had a programmer who wrote a utility to automatically look up prospect phone #'s, etc and insert them into the database. However, his utility is writing to the database structure I posted, with the one-to-many relationships which make it impossible to create an updateable view (to my limited knowledge). This programmer has since taken on another much larger project, so he's no longer available for a re-write, which is why I'm trying to accomplish this through SQL commands, as I am not a programmer.

Basically, I want a way to create an updateable view which includes the prospect name, address, comments, and phone number. This is why I'm trying to move the data into the table structure I posted above.

Thanks again for the reply!