Database question -> node and node_revisions, nid & vid

Database question -> node and node_revisions, nid & vid
Post installation · Drupal 5.xrconstantine - December 24, 2006 - 03:08
I'm pretty new to Drupal and am trying to get a handle on how the database works. I've been going through the latest system.install file in the system folder.

I see that the node table has a PRIMARY KEY comprised of both nid and vid. I see that nid is auto_incremented by node and so is generated when a row is added. Based on the code in node.module, it looks like node's vid comes from node_revisions. But although node_revisions has both vid and nid, only vid makes up the PRIMARY KEY, and it seems to come from somewhere else.

My questions are:

1) Where does vid ultimately originate and which tables use it?

2) Why isn't node_revisions' PRIMARY KEY also a combination of vid and nid? Isn't each row of node_revisions only associated with one node (nid)?

3) Is a unique node (ex. nid=22) actually a collection of nodes+revisions (ex. nid+vid=22+3, 22+2, 22+1)? Wait a minute - I just checked my database. It looks like for a particular node, that revisions start at that node's number. Is that right? What is the advantage of this? So my previous example would be 22+22, 22+23, 22+24... Nope. I guess vid is a running number which is really the total number of revisions on the site.

4) Are table column names reused where they actually don't mean the same thing? Ex. cid = comment id & cid = contact id??? Ex2. vid = reVision id & vid = vocabulary id??? That doesn't seem like a good idea. Especially since the DB isn't set up relationally (with foreign keys).

5) I see that several people have requested/worked on documentation for the DB, but it is incomplete and hasn't been worked on for quite a while. Has the dev team considered using a graphical tool to develop the DB in the first place? I know that some have commented that MySQL doesn't support foreign keys or something, but if the DB originated from an RDM, it wouldn't matter since the RDM file would maintain the relationships and the exported sql file would only contain the syntax relevant to the target DB. This would also make it easier to export the DB to currently-unsupported DBs. I've used CASE Studio for a few years and it does a pretty good job. I don't know whether there are open source solutions out there that are worth using or not.

6) Is this the place to be posting these questions?

Thanks folks. My company has already decided to make a long-term commitment to Drupal, so I appreciate the help.

» Login or register to post comments
I thought of another question...or two
rconstantine - December 24, 2006 - 03:25
Scratch my question from question 3 above. I see it doesn't work that way. In fact, it seems to me that the node in the node table never has more than one vid at a time, so the PRIMARY KEY doesn't need to be a combo of vid and nid. Likewise, the node_revisions table never has duplicate vids, so my suggestion in question 2 isn't necessary either.

Am I right?

Thanks again.

Login or register to post commentsAnother Q
rconstantine - December 24, 2006 - 03:47
nid in mode_comment_statistics is auto_increment in the system.install file, but it looks like the comment insert sql inserts using an existing nid from a node via:

case 'insert':
db_query('INSERT INTO {node_comment_statistics} (nid, last_comment_timestamp, last_comment_name, last_comment_uid, comment_count) VALUES (%d, %d, NULL, %d, 0)', $node->nid, $node->created, $node->uid);
break;

in the comment.module file.

So I take it that nid in node_comment_statistics shouldn't be auto_increment???

Login or register to post comments
I'd like to know these same answers!
gwideman - January 4, 2007 - 09:50
... and I've posted a question for the core folks here:
http://drupal.org/node/106774
... as they may have the details at their fingertips.

Login or register to post comments
+1 for some clarity
nasi - March 21, 2007 - 20:18
This is also an issue I've been scratching my head over.

I'm working on an ecommerce product type module based on tangible.module and the schema used by tangible is causing me some confusion.

The table it creates (ec_product_tangible) has both nid and vid columns but the primary key is on vid and only vid. My first mistake was to think that vid == vocabulary id and that made no sense at all. So from reading above I realise that is probably a revision id but it still doesn't make sense to me why this would be the primary key rather than nid.

Looking at other ecommerce tables, the ec_product table has nid and vid but neither of those is a primary key (there isn't one at all).

Looking further afield, I have noticed that CCK uses vid rather than nid as the primary key in it's content_type_ tables.

So it seems like node tables should be indexed on vid, but if someone could explain why then I would be a lot happier!

Login or register to post comments
vid is vocabulary id only in
sime - March 23, 2007 - 14:05
vid is vocabulary id only in the context of taxonomy. In the wider scheme it is more often revision. ecommerce is generally revision aware so you'll find most tables in ec with nid also have vid

Login or register to post comments
You might like to look at
sime - March 23, 2007 - 14:08
You might like to look at this page for some work on a scheme and some diagrams I did about table relationships.
http://drupal.org/node/22754

Login or register to post commentsIndex mayhem...
quaoar - March 25, 2007 - 23:27
Using drupal 5.1 and looking at the Node-table, I get this information for indexes:
PRIMARY KEY (`nid`,`vid`),
UNIQUE KEY `vid` (`vid`),
KEY `node_type` (`type`(4)),
KEY `node_title_type` (`title`,`type`(4)),
KEY `status` (`status`),
KEY `uid` (`uid`),
KEY `node_moderate` (`moderate`),
KEY `node_promote_status` (`promote`,`status`),
KEY `node_created` (`created`),
KEY `node_changed` (`changed`),
KEY `node_status_type` (`status`,`type`,`nid`),
KEY `nid` (`nid`)

As noted by several people, primary key for this table should really not consist of any other field than nid.
Also, why the extra index on nid (look at the last line)? That doesn't make any sense. PRIMARY KEY (nid,vid) already created an index on nid, even if it includes more fields, as long as nid is first in a multi-key it can also be used alone.
Same for the field "status"

Another thing is Unique key on vid, when it's already part of primary key.
Why?
If it's to be unique, it could just as well be the primary key?
This means that nid can contain multiple instances of the same value?
Because, if vid is to be unique, and primary key is to be made up of nid and vid, the conclusin have to be that nid can contain several instances of the same value. Which does not make any sense at all...

Login or register to post commentsmessy db
masteryan - April 14, 2007 - 00:21
this db is pretty inefficient

i was thinking node_revisions was useful
but it looks like all it does is hold the body

vid doesnt change when the node is updated.

i dont get the vid/nid thing either.
it looks like parts of the system uses
vid as the uid and parts use nid

ive seen this a lot
during legacy system rebuilds

one dev team created one standard
and the next team created another.

im not saying thats the case here,
but it sure looks possible with this group.

overall i like drupal.
lots of free clean code.

but my systems use flat files.
relying on a db to draw pages
craps out at about 10k uniques/day

anyone who creates a graphical map
of this db is a hero in my eyes..

Login or register to post comments
If revisions are not turned
sime - April 14, 2007 - 04:39
If revisions are not turned on, vid is no different from nid, and yes the extra 1to1 join between node and node_revisions could be seen as unfortunate.

With revisions turned on, nid 1toMANY vid

So some other tables will add information to the specific node (version unspecific) and some are "version aware" and will allow different information to be associated with different versions of the same node.

There is some documentation about database schema here although nothing exhaustive.

Login or register to post comments
nice graphic
masteryan - April 14, 2007 - 17:49
i like this chart. its pretty good:

http://drupal.org/files/issues/Drupal5RC1_Database_0.png

Login or register to post comments
I was wondering about that
rainer_f - February 2, 2008 - 06:35
I was wondering about that vid/nid thing too when I wrote my first module. In the end it's like that:

If your node type or node add-on or whatever you develop needs to be revision-aware -> take the (vid, nid) way. If it's not, ignore the vid (it's equal to nid).

See the example-module update source to make things clear!

Login or register to post comments
Indeed
greg.harvey - June 26, 2008 - 14:55
In other words, vid is a poor choice for either taxonomy or node - whichever was first! (Presumably whoever wrote taxonomy came second.) In many instances vid means VOCABULARY id and is related to taxonomy, but in node and content type circles I believe it is short for VERSION id and has *nothing* to do with taxonomy. It is used to identify a specific VERSION of a specific node. =)

--
http://www.drupaler.co.uk/

Login or register to post comments
Indeed +
jackspiv - February 6, 2009 - 17:04
As long as this is starting to be a catalog, I'll put my newbie 2 cents in.

Rummaging around in my database, I also find a lot of VID associated with the Views module.

Seems to be short for VIEW id ... doesnt seem to have anything to do with either Taxonomy or Version.

Maybe someone else could confirm?

Login or register to post comments
Yeah that's correct.
sime - February 7, 2009 - 01:31
Yeah that's correct.