CODEWITHSUNDEEP
mysqldatabasedatabase-design
curiosity4834
curiosity4834

Reputation: 73

Creating relational tables combined or separated? (in MySQL)

I'm creating a MySQL database and I'm not sure how to create relational tables for best performance.

I have ships and cars tables. Each car or ship will have a few pictures. I think I have two option.

1-) I will create one upload table -> id, parent_table, parent_id ...

2-) I will create two tables like upload_ship and upload_car -> id, parent_id

Which one is better for performance? Generally, must relational tables be separated or combined?

Upvotes: 1

Views: 59

Answers (1)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

Logically, they are separate tables, since constraints (foreign keys in this case) are different. You can later combine them in a single VIEW if appropriate.

Physically, there won't be much performance difference by default, but having separate tables allows you the flexibility of putting them on separate disks if your access pattern would benefit from that.

id, parent_table, parent_id

Never do that.

You are essentially preventing the DBMS from enforcing referential integrity for you. Attempting to enforce it from triggers or application code is actually not easy in a concurrent environment and the resulting code will likely have subtle race conditions and/or not perform as well as the "native" FOREIGN KEYs.

Generally, must relational tables be separated or combined?

This is a big topic. I suggest reading about database modeling and normalization. You may find ERwin Methods Guide interesting to that end - it's about specific product (a modelling tool called ERwin), but the concepts it explains are universal.

Upvotes: 3

Related Questions