Technicks on keeping database consistency

dominant

What technicks do you use in order to maintain your database consistency and correct relationship between your tables?

eoghain

Either use a database that implements relationships (INNODB tables in MySQL, or PostgreSQL, or jsut about any other D😎. Or don't let anyone but you access to coding against your database, and definately don't allow people to have direct access to an SQL interpreter for your database.

Stored procedures and triggers also work if your database supports them.

dominant

I work with the Mysql and i found that LAST_INSERT_ID() can do the job perfectly for the current connection id.

Is the above a common practice?

eoghain

It sounds like you are trying to insert to 2 tables at the same time and have them linked by an ID.

i.e insert into table 1, get auto_increment field value, insert into table 2 using id just obtained.

Yes that is a common practice. However when dealing with a system that could have more than 1 person doing something at the same time, I'd recommend that you use transactions. I don't remember if non-INNODB tables in MySQL are capable of using transactions, but you should look into it.

dominant

I thought that MyISAM tables was the best. By the way, what are the transactions in the mysql?

eoghain

No, MyISAM just happen to be the standard, but they are missing features like Foreign Keys, the best way to let your database do the dirty work of keeping your data correct.

Transactions are just that, discrete transactions. The way they work is you start a transaction do your queries and then finish your transaction. What the database does is let you run multiple queries against there own copy of the database and doesn't commit them to the database until you tell it to. Using transactions you wont run into this problem, that you would using no transactions:

User one logs into your page and submits data.
User two logs into your page and submits data, a second later.
User ones first insert query runs.
User twos first insert query runs.
User ones Last_Insert_ID() query runs returning 2.
User twos Last_Insert_ID() query runs also returning 2.
User ones second insert query runs attaching that data to ID 2.
User twos second insert query runs attaching that data to ID 2, if not failing due to duplicate data.

When building a multi user database application you can and will have situations where multiple people are accessing the same part of the app at the same time. Without transactions the timing of these peoples actions could overlap like in the above scenario, and given enough time it will happen. If that does happen your data is now corrupt and it could take a very long time to figure out what happened and how to fix it.

dominant

Originally posted by eoghain

User one logs into your page and submits data.
User two logs into your page and submits data, a second later.
User ones first insert query runs.
User twos first insert query runs.
User ones Last_Insert_ID() query runs returning 2.
User twos Last_Insert_ID() query runs also returning 2.
User ones second insert query runs attaching that data to ID 2.
User twos second insert query runs attaching that data to ID 2, if not failing due to duplicate data.

Look the user are totally application users. The script uses a single database username/password to connect with the database.

i think that LAST_INSERT_ID works separately for every connection id. That means every user accesses the page, receive a distinct connection id.

So the ids won't be the same for any users.

How transactions can be applied to php script, do they work for MyISAM?