CODEWITHSUNDEEP
mysql
David19801
David19801

Reputation: 11448

many to many which DB engine to use in MYsql?

I have a customer, middle, items type database.

Customers can buy many items and this is stored in the "middle" table, with just the two columns with customerid and itemid.

I am currently using myisam for all tables.

Is it recommended to use Innodb for the middle table in this structure?

Upvotes: 0

Views: 64

Answers (3)

Grim
Grim

Reputation: 1986

Innodb let you define cool foregin-keys.

From there you'll have some features:

  • foregin-keys can delete / update depending datas too.
  • foregin-keys can reject deletings/updates if dependings exists.

Usually, to use foregin-keys decides early: those foregin-key require extra-code-restrictions - i.e. you may not be able to insert middle without an customer or item.

Upvotes: 0

Phil
Phil

Reputation: 164776

INNODB supports foreign key constraints including cascading deletes which can be very helpful in a junction table.

Cascading deletes (put simply) allows your schema to clean up after itself.

If your junction table employs foreign key constraints with cascading deletes and you remove either a customer or item record, the matching middle record will be removed so it does not become orphaned.

I'd definitely recommend INNODB

Upvotes: 0

talha2k
talha2k

Reputation: 1

I would prefer InnoDB in this case because:

InnoDB should be used where data integrity comes a priority because it inherently takes care of them by the help of relationship constraints and transactions. Faster in write-intensive (inserts, updates) tables because it utilizes row-level locking and only hold up changes to the same row that’s being inserted or updated.

While,

MyISAM is simpler to design and create, thus better for beginners. No worries about the foreign relationships between tables. Faster than InnoDB on the whole as a result of the simpler structure thus much less costs of server resources. Full-text indexing. Especially good for read-intensive (select) tables.

The comparison is pretty straightforward. InnoDB is more suitable for data critical situations that require frequent inserts and updates. MyISAM, on the other hand, performs better with applications that don’t quite depend on the data integrity and mostly just select and display the data.

Hope this helps.

Upvotes: 1

Related Questions