id column in database tables

Yii 1.1 General Discussion
1

Hi everybody,

does Yii require that every table in the database has an id (with PK index) column?

I heard that some framework needs it, since I am choosing which framework to use I am asking about Yii.

Thanks!

2

Hi Ciro

No it is not required. Yii uses normal sql in the background, so you can create your tables with/without the same primary and/or secondary keys and indexes that you would normally do in any relational DB.

With Yii you can:

  1. findAll() - read all records, regardless of key/index;

  2. findByPk() - read single record by id;

  3. findByAttributes() - read secondary index (or any other combination of columns);

  4. findAllByAttributes() - same as above, but returns multiple records;

  5. multiple other ways to write your own sql - which is very nice because you can do things like using php if() statements to generate different sections of sql code.

However, the general advice (in most frameworks using relational DBs - not just Yii) is to give all your tables a single primary key. This can be an auto-generated id or a unique key you entered. Link your tables using this primary key as relation.

Then, if you need composite indexes, you can still create them separately to ensure your records are unique. But still, your records are linked using the unique primary key only (which the user anyway does not need to see). If needed, you can then show the user the data of the columns forming the composite index.

But, by not using the composite index to link your tables, you will save yourself plenty of work (in all frameworks).