I have recently started a web company in China using MySQL and PHP as the development environment. Apparently the common practice here is to not create relationships between tables. They would draw out the relationship in the logical ERD, but when it came to the actual DDL, they would only create the table without defining foreign keys.

Obviously I found this to be very strange. My staff explained that checks were done on the front end to ensure that there were no data integrity errors.

For example, we have 2 tables: State (State_ID, State_Name) and City (City_ID, City_Name, State_Name). These are independent tables with no relationship. To ensure a City is entered with a corresponding State that exists, they would just populate the combo box with the State names from the state table. And also have another check to ensure that the State entered (that is going to be saved to the City table) exists in the State table.

It seems that this is a viable way to do it, and the end result would be the same. But are there any consequences to proceeding this way, such as speed or other issues?

For the sake of illustration, imagine a bank that operates by putting its money into a large box in the lobby and hangs multiple clipboards with pages showing account numbers/deposits/withdrawls for customers to fill out when they come to deposit or withdraw money. As long as all of the depositors record correct information for every transaction, there will be no problems.

In the example that you describe in your post, every application is like a depositor in my example. As long as every appliction behaves exactly according to the rules, there will be no problems.

I'm a cynic, but I'd guess that this system would be stable for many milliseconds before problems started to occur. At the end of several days, the data would be worthless especially if there is a rogue or renegade application that doesn't use the rules.

Most implementations of MySQL can't enforce referential integrity. MyISAM will accept DRI (Declaritive Referential Integrity) specifications, but it won't enforce them. The MySQL Engine can be reconfigured to support DRI by using Inno-DB, but most of the public installations of MySQL that I have seen do not support Inno-DB.

You may be firmly lodged between a rock and a hard place.

-PatP

you don't need relationships to make a databse work, but then that rather negartes the justification to use a realtional database in the frist place.

as you point out you can enforce the data integrity by putting all the realtional checking in the application layer. but that means every write or edit action has to enforce those rules, whereras if its enforced at the databasae level then y'don't need to. As PatP points out that approachis fraught with potential pitfalls, all it needs is one developer not understanding the model and you will have problems.

As to whether public MySQL installations are MyISAM or INNODB often thats down to the user not specifying what they want from day one. many if not most ISP's can proovide either but the default is usually MyISAM