Shifting data from Oracle to PHP - foreign keys lost

andrewmoir

Hello guys/girls:

I wonder if you guys can give me an idea on the right way to go with regards our companies data.

I am competent but not advanced in PHP, HTML, MySql (admin) and VB6.

I part own and run a laboratory. From our instrumentation, data is pushed onto our database. It is then analysed and a comment/action is added to the data which is then faxed/ web-browsed / emailed/ posted to our clients.

Our current database is Oracle based, it was written for a number of similar laboratories in mind (I think its sold about 30 copies- note there are only about 200 laboratories similar to ours).

The current db is not ideal, I don’t know Oracle and so tread with fear. The interface for analysis is terrible, whenever new features are needed we have to cough up cash. We cannot easily add new fields for new equipment, cannot share data with other companies without exporting to excel only. The web version for customers to look at their data is slow and the pdf’s it generates large.

Database specifics

5 tables

Main table with all data : called Sample table.

Each sample gets assigned a unique lab number.

Each sample is arranged by 4 categories:

Customer - it came from
Site - which of their depots it came from
Machine - the machine the sample came from
Component - part of the machine it came from.

e.g.

11567, Generator Engineers , Observatory , Cat 114 , Engine , DATA………
11559, Generator Engineers , Observatory , Cat 114 , Differential , DATA…….
194, Miller Breweries, CT depot, Mercedes 1, Engine , DATA…………

Each of these 4 categories are ALSO arranged in tables in the database in what I assume are referential foreign key tables:
:the first table lists all the customers, the second table lists all the customers with sites e.g

GE, observatory
GE, Cape town
GE, Woodstock
Miller, CT depot
Miller, JHB depot etc.

The third table lists all all the customers with sites with machines e.g.
GE, observatory , CAT1
GE, observatory , CAT34
Miller, JHB depot, Mercedes 45

And the fourth table lists all the customers with sites with machines with components.

Now I assume this is how cascading foreign tables are structured, please tell me if not.

Now any new sample when registered will obviously have to pick its customer /site /machine /component from one of the rows in the fourth table.

Any changes made to say the 1st (customers) table replicate through the other tables to the specific samples belonging to that company (i.e. samples in the sample table)

Questions:
1.is the oracle above likely to be using cascading foreign keys, I assume so?
--. I want to export all the data into a MySQL table since BUT I can only export the date in excel format. All I get is these 5 tables but with no referential keys.:
3. can I create cascading foreign keys on data that has already been put in a new mysql database (I have tried and struggled + I can’t go throught the entire database and rebuild all the relationships)?
4. How difficult would is it be to have the programming itself ensure that all the fields are correctly set out without foreign keys. E.g. have just two tables 1 the sample table and 2 the categories set out like in the fourth cascading table? And whenver a new component is created the software ensures it is created in a existing customer/site/component. - the software ensure integrity and not foreign keys.
5. I realize that VB (or other) needs to be used to push the data from the lab instrumentation pc’s since they have to ‘listen’ for the data, but apart from this is a php web front considered a good front end for looking and editing my data (remember its on a local network)?

Please advise, regards
Andrew

bastien

Originally posted by andrewmoir
Hello guys/girls:

Questions:
1.is the oracle above likely to be using cascading foreign keys, I assume so?
--. I want to export all the data into a MySQL table since BUT I can only export the date in excel format. All I get is these 5 tables but with no referential keys.:
3. can I create cascading foreign keys on data that has already been put in a new mysql database (I have tried and struggled + I can’t go throught the entire database and rebuild all the relationships)?
4. How difficult would is it be to have the programming itself ensure that all the fields are correctly set out without foreign keys. E.g. have just two tables 1 the sample table and 2 the categories set out like in the fourth cascading table? And whenver a new component is created the software ensures it is created in a existing customer/site/component. - the software ensure integrity and not foreign keys.
5. I realize that VB (or other) needs to be used to push the data from the lab instrumentation pc’s since they have to ‘listen’ for the data, but apart from this is a php web front considered a good front end for looking and editing my data (remember its on a local network)?

Please advise, regards
Andrew

Hey Andrew,

Ref keys not required by db, so it may or may not be there. Your current application may handle all the db ref keys strcuture. Only db analysis will tell if its there. Do you have an open query interface that will allow you to run ad hoc queries against the db?
Mysql supports ref keys using the Innodb engine. Set up of the ref keys must be done in stages since the fields to be indexed as FKs mush have an index created on them first. And then the ref key index placed on it. You can add it after the data is inserted but you may run into contraints if there is a ref integrity failure in your data
Just need to query the tables to ensure that certain elements exist. It will slow the application down some but if the insert load is not too high, it shouldn't matter
yes, it should meet your needs nicely. with some creative scripting you should be able to use PHP to move the data around on the machines as well, or perhaps consider moving to a full web interface if your requirements allow it.