Consequences of not creating relationships between tables?

[ptflyer]

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?

[BitBlt]

This has been an ongoing debate ever since relational database engines were first created. On the one hand, you have the group that says "Data integrity outweighs a minor speed reduction." On the other hand, you have the group that says "Speed is most important...don't slow it down for anything...we'll worry about data integrity in the front-end." On the other hand (three hands?) there is a group that says "You should use a combination of referential integrity, triggers and stored procedures so your front-end can't orphan your data or accidentally mess it up."

Personally, I don't take sides. Like any good Data Administrator (note: NOT DBA) I say "It depends."

For relatively static data (like State Names) referential integrity is really less of an issue. You just have to ensure that there is a periodic data scrub built into your application so that when someone fat-fingers "North Dakota" as the state when they entered "New York" as the city, it can be corrected. But for applications where absolute accuracy is vital (think Medical Records) then referential integrity is worth the cost in speed.

That being said, modern DBMS's and computer hardware have come a long way since the days when implementing referential integrity had a substantial performance impact. Obviously, if RI is poorly designed it will adversely impact presentation or maintenance of data, but any reasonably competent DBA should be able to do it properly.

One of my favorite sayings is "No computer or system is so powerful that some clever programmer (or DBA) can't make it run slowly." I bring this up to point out that building a client-server database application is definitely a team effort (like that's news?). Your database design can be the greatest since Codd published his thesis, but if the front-end doesn't access it well, it will appear slow. Conversely, your program can be the most efficient, well structured and tightly designed code since electrons were invented, but if it has to wait on database response, it will still appear bad. And, if your technical team spends weeks trying to squeeze out that last pico-second of performance and your user says "Meh..." then your team still looks bad.

The final issue is, how easy or hard will it be to troubleshoot if something goes wrong, or how easy/hard will it be to add features and functionality to your application in subsequent releases? Does your maintenance staff have the necessary skill to not break everything if they "tweak" the app? This is one of those ongoing questions that will drive you nuts until the retirement of the app. Again, there is no good answer...it depends on the complexity of the app, the service level agreement you have with your user community and the criticality of data accuracy.

There, now. After my long response, you still probably don't have an answer. Obviously only you or the people you trust there can make these decisions, but I hope at least I've given you some things to think about. Good luck!