Managing Relationships in MySQL

[dereko]

Hi,

 

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 Client

id PRIMARY

name 



>

TABLE Product

id PRIMARY

client_id

name 



>TABLE Project

id PRIMARY

product_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\'.

 

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.

 

Also is there any way of recording relationships between tables in mysql. And then a tool to automatically create a dababase design diagram?

 

I post this as discussion as much as a problem that needs to solved, anyone any thoughts on this kind of thing?

 

Does Oracle(I have no experience with it) for example handle relationships better?

 

Derek

[gizmola]

You managed to pack in quite a few questions here, I\'ll try and answer them:

 

Hi,

 

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 Client

id PRIMARY

name 



>

TABLE Product

id PRIMARY

client_id

name 



>TABLE Project

id PRIMARY

product_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?

 

Derek

 

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.

[dereko]

Ok, I was a bit vague after reading it again.

 

I have a situation in my database design where

 

Clients can have many Brands

Brands can have many Products

Products can have many Projects

 

Now basically this PHP/Mysql application that allows this company to keep track of all there work they do for there clients among other things. The application allows the user to add/edit/change status(active, inactive) of new clients,brands, products and projects.

 

The problem as it where is,

 

A project may be assosicated with a product and/or brand. But I must also allow for a situation where there is no product or brand assosciated with the project. So effectively it is just associated with a client.

 

So what I did and admittly I\'m not sure it is the best way to do it, is: When a person adds a project that is not assosciated with a brand or product I create a new brand and new product with the same name as the client so this way I know if client name = brand name then there is no brand assosicated with this project etc. Also if the \'default\' brand or product already exists then I just hang the new project off the already created default brand or project.

 

You see when I started out doing the database design, I couldn\'t see a way to allow for this in just pure MySQL/database design, so I made this ad hoc way of dealing with it.

 

So from what you see there, is there something I could have done different to make it more simplier.