Database Schema for Plugin and for WP in General

[WPSardine]

Hi Folks,

I am working on a site-specific plugin that will allow the admin to create a number of entities that are associated through a project. For this plugin, I will be creating custom post types for Person, Funder, and Topic. These types will be associated in a one-to-many relationship with another custom post type, Projects. In other words, each Project will have many Persons, Funders, and Topics. Through these associations, I will be able to look up any of these custom types and see other types that are associated with them.

My question is this; what would be the best way to associate Person, Funder and Topic with Projects? My previous experience with database schema would have me create association tables that would hold fields for something like person_id and project_id, which would create an association between these two types. I want to make sure that I'm not unnecessarily creating a table in WP, so I thought I might check in here to see if this was a good solution or not.

I did consider create a new row in postmeta for each project that holds the associations. My issue with this structure is that it would be difficult for me to look up Person and see which projects are associated with that person. To do so, I'd have to query for every project association row in the postmeta table and examine it to see which projects matched the person.

My other question is about the WP database structure in general. I'm a self-taught php programmer, so my knowledge isn't fully rounded out. My understanding of good database structure is that the schema is normalized. From my understanding of this concept, the WP database is not normalized. I'm assuming that this database structure follows another theory. Could anyone enlighten me to what this theory might be? I'd like to read more about it.

Thanks so much!

[andrea_r]

Have you seen these?

http://codex.wordpress.org/FAQ_Devel...abstraction.3F
http://codex.wordpress.org/Database_Description

[WPSardine]

Andrea...thanks for the links. I have seen those pages before. I actually use the database description page frequently as it very clearly lays out the database. In fact, if I had an office dedicated to doing web development, I'm sure I'd have the database schematic posted prominently ;)

Thanks for your response. I'm still looking for some insight into whether my proposed structure is a good way to tackle my plugin and also information about the type of database structure used by WP. I think this can prove to be a learning experience for me.

[andreasnrb]

Check the stuff Justin Tadlock has written about custom post types and taxonomies etc. His moviedatabase is similiar in structure I think.

[WPSardine]

Thanks for the reply Andreas. Strange that the replies have been from Andrea and Andreas...

Anyway, I follow tadlock's blog. He's brilliant and does an amazing job explaining cutting edge features of WP. I took a second look at his movie database example and it's not quite what I'm looking to do. He utilizes custom taxonomies to organize his posts (these should be custom post types, but the feature was not available when he published the post). Instead of creating custom taxonomies for my projects, I would like to create multiple custom post types and associate them. I could not do this with creating the custom post type "Project" and then creating custom taxonomies for "Person," "Funder" and "Topic" as each of these entities will have lost of information to enter for them. I think it is still best to treat them as custom post types and not taxonomies.

Thanks for the suggestion! Re-reading the movie database post did remind me that in another project I should have created a custom taxonomy instead of creating a meta box that allowed a user to indicate whether an album is vinyl, mp3 or CD. I need to go leverage the power of custom taxonomies for that project. Thanks for the reminder!

Anyone have other thoughts on my original question?

Thanks all for the wonderful responses so far!

[hakre]

My question is this; what would be the best way to associate Person, Funder and Topic with Projects? My previous experience with database schema would have me create association tables that would hold fields for something like person_id and project_id, which would create an association between these two types. I want to make sure that I'm not unnecessarily creating a table in WP, so I thought I might check in here to see if this was a good solution or not.

Well it's done like you already write. And it's not creating a Table in WP, you're creating a table for your plugin which can be seperate from WP tables - you just share the database configuration credentials.

My other question is about the WP database structure in general. I'm a self-taught php programmer, so my knowledge isn't fully rounded out. My understanding of good database structure is that the schema is normalized. From my understanding of this concept, the WP database is not normalized. I'm assuming that this database structure follows another theory. Could anyone enlighten me to what this theory might be? I'd like to read more about it.

I do not know how theories are called when there is not specific design or pattern followed. Maybe it's an Anti-Pattern database design?

From what I remember, there never went much design into that database structure, it just grew over time. As you still can see today, there is not much abstraction about it, and the database access class is more or less a database associated code container and that's it. Most code is directly connected with the database queries. There never went much innovation in there, nothing specificially designed or so. What was working, was taken. I do not know how that is called in English.

[andrea_r]

But certain tables are indexed. ;)

[WPSardine]

Well it's done like you already write. And it's not creating a Table in WP, you're creating a table for your plugin which can be seperate from WP tables - you just share the database configuration credentials.

Ok...I guess I'll go this route. I'm creating an extra table in the database in which the WP tables exist. I prefer solutions where I don't have to do this, but it seems this is the only way.

I do not know how theories are called when there is not specific design or pattern followed. Maybe it's an Anti-Pattern database design?

From what I remember, there never went much design into that database structure, it just grew over time. As you still can see today, there is not much abstraction about it, and the database access class is more or less a database associated code container and that's it. Most code is directly connected with the database queries. There never went much innovation in there, nothing specificially designed or so. What was working, was taken. I do not know how that is called in English.

This is interesting. Everything (well almost everything) seems so well designed and thought out in WP, I'm surprised that there wouldn't be a more calculated approach to the database schema. In fact, I imagine there must be, but perhaps it doesn't follow a specific design like I am seeking.

Thanks for your response!