Thread

Is there any documentation available that describes the EE database structure? In particular, it’s tricky trying to work out which fields constitute the primary key on some of the tables.

I don’t think there is - really just manually looking at the database is how I’ve done it.  I think the primary key is always the first field in the table unless I"m mistaken?

Thanks Brian,

Quite a few of the tables have composite primary keys consisting of two or maybe even tree fields.

For example the exp_template_member_groups table includes just the fields group_id and template_group_id which link to the exp_field_groups and exp_template_groups tables respectively.

There are others tables where it isn’t so obvious including some where a date field may or may not be part of the composite key.

The exp_template_member_groups table has no primary key at all does it?  I’m using phpMyAdmin and it shows group_id is indexed, but no primary.  For example if you look at exp_weblogs - weblog_id is the primary, and there are 4 indexed columns - cat_group, status_group, field_group, is_user_blog.

Those columns are linking entries to other tables, but they aren’t unique, which is what the primary key is. Right? Dates are odd - unless it’s changed in a recent point release, there’s dates in different formats in different spots of the DB. Keep an eye open for that!

Oh crap - I just accidentally deleted the database of one of my sites. (Must make backups, must make backups, must make backups.) That big sucking noise is the sound of several day of work going down the plug hole. Ho hum back to the drawing board.

Anyway, a primary key can be composed of one or more local fields or one or more foreign key fields, as is the case with the exp_template_member_groups table. Where a primary key is a composite of two fields, then the combination of the data in these must be unique.

I’ve set up an MS Access database now with linked tables to the EE MySQL database and can see the primary keys in table design view.

The exp_password_lockout table is one which uses a date field as part of a triple field composite key.

You’ll have to forgive me because I’m not formally trained (I’m self-taught), but really the composite primary key you’re talking about isn’t a ‘real’ primary key, is it?  You can enter duplicate entries in the exp_password_lockout table - I just entered two identical ones just to test it.  There isn’t an actual primary key in there at all?

In a certain sense you can think of it as a composite primary key simply because through the normal use of the software you won’t ever see identical entries, but it’s not a true primary key as seen from the database itself?

Brian is right.
exp_password_lockout has no primary key, just indexes.
In the standard EE installation there is currently no table that has a composite primary key. I haven’t checked all additional modules though.

I really keep meaning to post a list and diagram of the whole EE database schema. If enough people express interest it’ll probably get me to do it faster. So, express interest here!

On a scale of 1-10 with 10 being highest my interest is a kazillion. 😊

I really keep meaning to post a list and diagram of the whole EE database schema. If enough people express interest it’ll probably get me to do it faster. So, express interest here!

That’d be really handy. Someone once did one, but IIRC it was back round V 1.3-ish.

Huh. The schema hasn’t changed THAT much, so it’d probably still be reasonably helpful, but i never saw that at all. I’ll do a forum search and see if i can at least use it as a starting point.

I would think there is software that does this automatically?

Brian M.,

Sort of, but in my experience it tends to create pretty ugly and unreadable diagrams. The listing functions are usually fine, and i’ll be using software to do that part. But for diagramming, you really can’t beat hand-creating one (again, in my experience.)

I’ve made a start on documenting the schema.

I used SQLylog to generate an html output of the schema, copied it to Word, tidied it up and added a summary of the index fields (page 2). It’s attached to this post.

However, working out what the various key relationships are is still the challenge. Most are obvious. Others not. If someone is keen to help, and knows the database structure well, in the index summary there is a column where you can list the foreign keys. Enter each field on a separate row in the form of table_name|field_name.

Cheers
Pico

Wow that looks like a lot of work!  Ummm - and don’t take this the wrong way - but isn’t this just the same thing you would see if you look at the DB in something like phpMyAdmin? It seems like it would be really difficult to use this wouldn’t it?  You either print it out and have to flip through tons of pages, or you have to scroll through tons of pages in Word, when you can get the same thing with a couple clicks with a DB tool?  Or does this include info you can’t easily get in those tools?