MySQL - link data between records and tables

[snorky]

I inherited a database that is one flat file rather than a multi-table relational database. I offered to rebuild the data into multiple tables. My offer was 'politely' declined because 're-creating the many reports would take too long.'

 

I get the hint, but I think the real problems are:

• my boss doesn't understand relational databases
  
• he's proud of this POS that he built and doesn't want to admit that it is, um, a POS
  

So I'm stuck with this.

 

The data change amazingly frequently. As a result, there is quite a bit of management required.

 

Many "pairs" of records are identical except for one or two fields. I'd like to have the contents of a field in one record be re-used in a field in another record. That would improve the integrity of the data. A quick audit of the database reveals many errors where a field in one row should - but does not - match what should be the data in a field in a different record.

 

Consider this:

select,rec,lname,fname,title,more,school,phone,ssnum,emplnum from main where title="Counselor";

The result set is

+-----+------------------------+---------+-----------+-----------+--------+----------+-----------+---------+
| rec | lname                  | fname   | title     | more      | school | phone    | ssnum     | emplnum |
+-----+------------------------+---------+-----------+-----------+--------+----------+-----------+---------+
|  95 | Smith                  | John    | Counselor |           |  ASE   | 269-5447 | 555111222 |  12345  |
+-----+------------------------+---------+-----------+-----------+--------+----------+-----------+---------+
|  96 | Counselor - John Smith |         | Counselor |           |  ASE   | 269-5693 | 555111222 |  12345  |
+-----+------------------------+---------+-----------+-----------+--------+----------+-----------+---------+
| 110 | Smith                  | John    | Counselor |           |  BRE   | 269-5116 | 555111222 |  12345  |
+-----+------------------------+---------+-----------+-----------+--------+----------+-----------+---------+
| 125 | Counselor - John Smith |         | Counselor |           |  BRE   | 269-5116 | 555111222 |  12345  |
+-----+------------------------+---------+-----------+-----------+--------+----------+-----------+---------+
|  16 | Jones                  | Jane    | Counselor |           |  CSE   | 269-5555 | 551413332 |  54321  |
+-----+------------------------+---------+-----------+-----------+--------+----------+-----------+---------+
| 119 | Counselor - Jane Jones |         | Counselor |           |  CSE   | 269-5555 | 551413332 |  54321  |
+-----+------------------------+---------+-----------+-----------+--------+----------+-----------+---------+

When John Smith changes schools - or adds another school to his "route" multiple records have to be updated. Clearly that has not always happened; predictably, the reports are unreliable.

 

Since I can't use multiple tables - "because of the reports" - is there a way I can link data between rows?

 

A possibility I considered is to create a proper database with multiple tables, and then

[*]Link the fields in the original database to the data in the new "master" database. Some fields in a record would contain a formula rather than a char or int (or whatever...) To do that, however, I need the same ability: linking data across records in a table(s).

[*]at report time create the funky database 'on the fly', as a temporary table, and run the report from the temporary table; however, that might get me in trouble.

 

Notes:

• What is shown above is a condensed version of the real db. There are more fields, and there is a more complex problem of  maintaining it.
  
• I improved the management somewhat by using "insert/select from"  queries to create new records - instead of entering the duplicated data into each field in the new record. That doesn't solve the real problem: how to synchronize data when there is an update to a field ... and how to delete all appropriate records when an employee leaves or is promoted.
  
• I built a data entry/record deletion form using PHP. That's what I use as a management tool. But when I'm away the boss takes care of the data, he still uses his old way - with predictably bad results. Sometimes I wonder why he thought he needed a DBA.
  
• I offered to do this as a spreadsheet, rather than as a database. Again, the offer was declined.
  
• The mysql server is version 4.1.20. However, I can move the database to a server that runs mysql 5.x I also have a 3.23 server available q[^_0]p
  
• Yes, I should push the better solution, but I'd rather keep my job than be right. Meanwhile, I do get more freedom on new DBs that I create
  

[gassaz]

Maybe you can use triggers and stored procedures to emulate a data link.... i know a crazy idea

[Ken2k7]

I have no idea why the telephone numbers are different in the first 2 rows. But since it makes no sense to have any by 'Counselor - FirstName LastName', you can just remove them from the database right? Or are you going to get in trouble for that?

 

There's nothing bad about the table. It's usable. Better than a lot of the ones I've seen posted here.

[snorky]

The phone numbers are different because of the problem that I'm trying to address - sloppy code leads to sloppy results. That, and my fat fingers ... when I condensed the real report  for clarity in this thread, I screwed up.

 

The table is really just a spreadsheet with a nice query language.... and that nice data entry form that I built.

 

Editorial: it's sad when politics triumph over technology. I find myself plotting ways to have it both ways: do the job right, and then as part of "my" system's functioning have it update the boss's table - and build a procedure to automate updating "my" tables when the boss has updated "his". That post suggesting triggers might be the key to making the subterfuge work. Or perhaps I should just live with it....

[fenway]

Not sure exactly what you're asking for...

[snorky]

Can I link data between rows?

[fenway]

Can I link data between rows?

Well, you can link the rows... how would linking "data" work?

[snorky]

Many "pairs" of records are identical except for one or two fields. I'd like to have the contents of a field in one record be re-used in a field in another record. That would improve the integrity of the data. A quick audit of the database reveals many errors where a field in one row should - but does not - match what should be the data in a field in a different record.

 

Think about a spreadsheet: for example,

in G78 I can use a formula like =$C$6

The values in G78 are the same as the values in C6.

 

That is what I mean by linking.

[fenway]

Yeah, that's not how a database works.

 

How do you decide which one of the N rows is correct?