You managed to pack in quite a few questions here, I\'ll try and answer them:
I\'ve have a fairly good idea about database design, normalisation etc..
I have an unusual situation, This is a basic view of what I have
>TABLE Clientid PRIMARYname >TABLE Productid PRIMARYclient_idname >TABLE Projectid PRIMARYproduct_id name
Now the way it works is, if a person enters in a new project that has no product it creates a default product with its \'name\' the same as the client \'name\'.
My problem is, if a person then edits the client \'name\' i need it to automatically update the the default product \'name\'.
Already you have lost me. The purpose of having a product table is that there should be a row for every product. You indicate that you are creating products that are really \"clients\". This is confusing and my kneejerk reaction is that your design needs work.
Now I can just add this to my php code so it does it, but is it possible to set it up in mysql so that if a certain field must always equal another field in differnt table.
This requirement would indicate there should be some relationship between the two tables, probably where one has a foreign key, but again exactly what is not clear from your description of the requirements of the application.
Also is there any way of recording relationships between tables in mysql. And then a tool to automatically create a dababase design diagram?
In RDBMS parlance, what you\'re referring to would be called declarative referential integrity. Mysql does not support this.
DRI is good for a few types of things: data driven constraints (table A has TableB.id as a foreign key, database will disallow if you attempt to insert a row where the value of TableB.id does not exist in a row in TableB) or cascading deletes (TableB is a child table of TableA, you delete a row in TableA and all the child rows in TableB would automatically be deleted). It\'s also useful to some tools in being able to reverse engineer the design of an existing database into a database design tool. One product that I\'ve recommended in the past (Dezign for databases) has an importer capability that will create a database design from an existing mysql database, however, it may not be able to actually create the relationships that were intended. While I have used Dezign for databases to design databases and generate the DDL sql to create the tables and indexes, I don\'t have the reverse engineering script so I can\'t really vouch for it\'s capabilities.
If your structure is as simple as you describe you might benefit from using somethign like Dezign and simply starting over by creating a design based on what you currently have, and then dropping and recreating it from the Dezign generated scripts. Having designed databases for years, I always use this methodology for my projects, since it guarantees that my database structure is what I think it is, not to mention the benefits of having design diagrams and table and attribute notes to refer to when I am developing.
Does Oracle(I have no experience with it) for example handle relationships better?
Yes Oracle supports a host of features that you will not find in Mysql, but then again, Oracle costs many thousands of dollars. In my opinion Oracle is the best RDBMS product available, and it\'s list of capabilities is unmatched by any competing product, however lack of DRI is not something that will prevent you from creating a functional reliable application so long as your database design is sound.
My phpfreaks tutorial
has several diagrams I created with Dezign for databases that illustrate the type of thinking that should go into figuring out what your database design should be. I can see that you have done some thinking about it, and have a grasp of some of the concepts of normalization, but without knowing more about the purpose of the application, I can\'t really help you further unfortunately.