Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2011
    Posts
    1

    Unanswered: Consequences of not creating relationships between tables?

    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?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •