CODEWITHSUNDEEP
mysqldatabase-design
Rich Standbrook
Rich Standbrook

Reputation: 648

Is it better to have one large table or should I use several?

I'm building a new application that has a number of data objects and each one needs "history" or notes. In the past I have just created one database table called notes and had a number of foreign keys attached to the different objects. This time I would like others thoughts. Is it good practice/efficient to use one table with ever increasing auto_inc IDs or should I maintain different [object]_notes type tables?

N.B. The Notes object itself would always be the same, subject, text, date etc.

Upvotes: 0

Views: 150

Answers (2)

Stephane Gosselin
Stephane Gosselin

Reputation: 9148

I think the question you are asking is if an auto incrementing ID is as good a primary key as composite natural keys, or a key composed of 2 entities.

Unless you have a good reason to do so, I would stick to the autoincrement Primary Key, it has a unique index thus optimized for read lookups. You can do still do an index on composite keys. Some actually prefer it that way as it can be argued that it makes the relationships clearer & clenaer by not having the extra column on each table, but for small applications and datasets I don't worry about that and just use the autoincrement option.

Upvotes: 0

Linora
Linora

Reputation: 10988

I'd use only 1 table. I assume we're not talking gazillions of history notes?

If not, then 1 table is just fine

Upvotes: 1

Related Questions