Does RedBean need a "id" primary key?

Question

If you use RedBean ORM, do you need to add a primary key named "id" to every table in your database?

In my db I have a few tables that have primary keys pairs of 2 or 3 fields, or primary keys with other names than "id" (yes, I could change the name to "id", but it wouldn't really reflect the reality, because they are not IDs)

Example:

table1 - stores posts:

  id           INTEGER      PRIMARY KEY AUTOINCREMENT,
  name         TEXT,
  title        TEXT,
  content      TEXT,

table2 - stores meta for posts:

  post         INTEGER      DEFAULT 0,     # <- references "id" from "posts"
  name         TEXT,
  value        TEXT,
  PRIMARY KEY(name, post),
  CONSTRAINT posts_meta FOREIGN KEY(post)
    REFERENCES posts(id) ON DELETE CASCADE ON UPDATE RESTRICT

Would RedBean work with this kind of db structure?

Interposition

As far as I know it has to have id as the primary key. I have tried it on tables without or with table_id and it wouldn't work. There may be a way to customize it to work, but I haven't tried. – Lament

Answer 1

Unfortunately, with how your current table structure is, you couldn't use RedBean. Every table needs to have an auto-increment primary key. A slight downfall, as it makes integration into an already existing product more difficult.

A couple of threads that failed to use RedBean due to this constraint, with responses from the author, Gabor De Mooij:

http://groups.google.com/group/redbeanorm/browse_thread/thread/6d5582275326744f?pli=1

http://groups.google.com/group/redbeanorm/browse_thread/thread/4fa7b29b453dcdb8

RedBean does NOT require the primary key field to be named simply "id", however. You can format the name of the primary key column to your liking by using the formatBeanID() method, as seen in the example below, which prefixes the table name to the "id" conditionally. eg) table users would have the primary key be users_id. Using that formatting, you can get as detailed with the id name as needed.

http://redbeanphp.com/community/wiki/index.php/Prefixes

Hopefully this restraint will be lifted in the future, since it really does hamper the integration into existing products.

EDIT: As an alternative ORM, I've heard well of Doctrine: http://www.doctrine-project.org/. I haven't personally used it, but it seems to be the standard for many working with PHP.

EDIT 2: Thanks and credit to Jason for bringing to attention a new method for integrating RedBean into an existing project where your database might not be set up for it. I wanted to update my answer as well in case people still reference it with this problem. Gabor suggested making views that map to the tables, where you can set up the view to have the proper structure required for RedBean. I have not personally tested this, but it has gotten positive feedback from some users. It adds some extra overhead and maintenance when altering tables, but seems to be the best and most complete answer to this issue to date. http://www.redbeanphp.com/faq#beanformatter

Nature

RedBeanPHP 3.0 change log: "... Removed Bean Formatter you can NO LONGER customize database schema (because it breaks things) ..." I use ReadBean v3.2 and what should I do? – Disconcert

@Ostrovski If you're trying to integrate RedBean into an already-existing project/database, and you need the functionality provided by the BeanFormatter, I'd implement RedBean 2.2.3. I'm not sure the reasoning to drop the BeanFormatter... It seems RedBean is moving towards the "new projects" domain, where you implement it and the database hand-in-hand. Check if you are able to implement 2.2.3, or if you want to/must use 3.0+, do some reading up on perhaps re-implementing those classes, as suggested in this post: groups.google.com/forum/?fromgroups=#!topic/redbeanorm/… – Nature

Answer 2

The accepted answer is not strictly true... You can use the existing table structure - but you would need to implement a VIEW on top of each of the tables that allow you to rename the PKEY column to be 'id'... See this email from Gabor - the creator of RedBeanPHP:

https://groups.google.com/forum/#!searchin/redbeanorm/view/redbeanorm/wXUeT4Tj2uU/3AngnmVwZdYJ

Messroom

By the responses to his suggestion, this looks to be a viable solution. That is actually the same link that I noted in my comment, it looks like the thread has remained active, and Gabor offered up this potential solution recently. I'll edit my answer to make sure anyone that comes to this page won't run into old, outdated, or incorrect advice. Thanks for the update! – Nature

You can also use the exec() function to do whatever you feel. ID is only for beans, you can still insert into / update however you like. – Spoliation