Taxonomy Schema Proposal

[Jamie Talbot]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ryan Boren wrote:
> On 4/19/07, Jamie Talbot <wphackers@...> wrote:
> I'm okay with a separate table as long as we're not joining on it
> everywhere.  The table could hold an auto_increment integer id and a
> varchar descriptive name.  This could be cached in an array stored in
> the options table.  Options are autoloaded so we won't be adding extra
> queries for front page loads.

That seems like a good solution.  So, should I be thinking about a new Trac ticket and putting some
code down for this now, or more consultation required?

Cheers,

Jamie.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGKXcNrovxfShShFARAt8nAKCLoB2KfidnRqxS4Xla02JHQcyZOwCfYN+/
VpGuzXSdziTbC8DroOGQyIs=
=eNTe
-----END PGP SIGNATURE-----
_______________________________________________
wp-hackers mailing list
wp-hackers@...
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Ryan Boren]

On 4/20/07, Jamie Talbot <wphackers@...> wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Ryan Boren wrote:
> > On 4/19/07, Jamie Talbot <wphackers@...> wrote:
> > I'm okay with a separate table as long as we're not joining on it
> > everywhere.  The table could hold an auto_increment integer id and a
> > varchar descriptive name.  This could be cached in an array stored in
> > the options table.  Options are autoloaded so we won't be adding extra
> > queries for front page loads.
>
> That seems like a good solution.  So, should I be thinking about a new Trac ticket and putting some
> code down for this now, or more consultation required?

Bust a move.

Ryan
_______________________________________________
wp-hackers mailing list
wp-hackers@...
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Mark Jaquith]

On Apr 20, 2007, at 3:25 PM, Ryan Boren wrote:

> I'm okay with a separate table as long as we're not joining on it
> everywhere.  The table could hold an auto_increment integer id and a
> varchar descriptive name.  This could be cached in an array stored in
> the options table.  Options are autoloaded so we won't be adding extra
> queries for front page loads.

Why not just have it stored as an option only?  It's not like there  
are going to be a lot of them, and the default ones can be assumed.  
Create an API for it.

function register_taxonomy($name) {
        if ( !taxonomy_exists($name) )
                return (int) create_taxonomy($name); // returns taxonomy id
        return (int) get_taxonomy_id($name);
}

And so forth.

Plugin would do:

define('MY_PLUGIN_CUSTOM_TAX_ID', register_taxonomy
('my_custom_taxonomy'));

If it needed to do custom queries on that taxonomy id.  Otherwise,  
the built-in functions would do quick queryless taxonomy_name =>  
taxonomy_id conversion out of the in-memory options cache  
( get_taxonomy_id($name), for instance).

--
Mark Jaquith
http://markjaquith.com/

Covered Web Services
http://coveredwebservices.com/


_______________________________________________
wp-hackers mailing list
wp-hackers@...
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Ryan Boren]

On 4/20/07, Mark Jaquith <mark.wordpress@...> wrote:

> On Apr 20, 2007, at 3:25 PM, Ryan Boren wrote:
>
> > I'm okay with a separate table as long as we're not joining on it
> > everywhere.  The table could hold an auto_increment integer id and a
> > varchar descriptive name.  This could be cached in an array stored in
> > the options table.  Options are autoloaded so we won't be adding extra
> > queries for front page loads.
>
> Why not just have it stored as an option only?  It's not like there
> are going to be a lot of them, and the default ones can be assumed.
> Create an API for it.
>
> function register_taxonomy($name) {
>         if ( !taxonomy_exists($name) )
>                 return (int) create_taxonomy($name); // returns taxonomy id
>         return (int) get_taxonomy_id($name);
> }
>
> And so forth.
>
> Plugin would do:
>
> define('MY_PLUGIN_CUSTOM_TAX_ID', register_taxonomy
> ('my_custom_taxonomy'));
>
> If it needed to do custom queries on that taxonomy id.  Otherwise,
> the built-in functions would do quick queryless taxonomy_name =>
> taxonomy_id conversion out of the in-memory options cache
> ( get_taxonomy_id($name), for instance).

That's fine too.  I think as long as we keep the ID strictly
incrementing and keep track of the current highest ID we are good.

Ryan
_______________________________________________
wp-hackers mailing list
wp-hackers@...
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Matt Mullenweg]

Mark Jaquith wrote:
> Why not just have it stored as an option only?  It's not like there are
> going to be a lot of them, and the default ones can be assumed.  Create
> an API for it.

Perhaps we could learn something from our roles/caps implementation
here, and hardcode the baseline/core functionality (categories and tags)
but allow for it to be extended with plugins, either with filters or in
the DB as you suggested.

--
Matt Mullenweg
  http://photomatt.net | http://wordpress.org
http://automattic.com | http://akismet.com
_______________________________________________
wp-hackers mailing list
wp-hackers@...
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Ryan Boren]

On 4/22/07, Matt Mullenweg <m@...> wrote:
> Mark Jaquith wrote:
> > Why not just have it stored as an option only?  It's not like there are
> > going to be a lot of them, and the default ones can be assumed.  Create
> > an API for it.
>
> Perhaps we could learn something from our roles/caps implementation
> here, and hardcode the baseline/core functionality (categories and tags)
> but allow for it to be extended with plugins, either with filters or in
> the DB as you suggested.

I think doing it through filters is too transient.
Activating/deactivating plugins could cause the IDs to shuffle.
Hardcoding the core taxonomies is fine for now, but what if we add
another core taxonomy down the road?  Its ID can't conflict with a
taxonomy ID that a plugin grabbed.  We could reserve a block of IDs
for use by core, I suppose.


Also, do we want to store some meta information for taxonomies.  For
example, a hierarchical flag that says whether the taxonomy can do
hierarchies.  For taxonomies that don't support hierarchies, we can
avoid running hierarchy specific code and queries in our generic terms
implementation.

Ryan
_______________________________________________
wp-hackers mailing list
wp-hackers@...
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Ryan Boren]

I opened a ticket here:

http://trac.wordpress.org/ticket/4189

Let's start throwing around some code.

Ryan
_______________________________________________
wp-hackers mailing list
wp-hackers@...
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Elias Torres]

I implemented tags on Apache Roller not long ago and we decided not to
create a terms table. We only have a table for entry2tag and a table for
aggregates (tag clouds).

create table roller_weblogentrytag (
    id              varchar(48)   not null primary key,
    entryid         varchar(48)   not null,
    websiteid       varchar(48)   not null,
    userid    varchar(48)   not null,
    name            varchar(255)  not null,
    time            $db.TIMESTAMP_SQL_TYPE not null
);

create table roller_weblogentrytagagg (
    id              varchar(48)   not null primary key,
    websiteid       varchar(48) ,
    name            varchar(255)  not null,
    total           integer  not null,
    lastused        $db.TIMESTAMP_SQL_TYPE not null
);

Basically, we store for every entry, one row per tag. This eliminates
most joins. Additionally, we have an aggregate table per website that
keeps track of the total number of times a tag is used. Remember Roller
is the equivalent of WP-MU, not WP.

Anyways, just a thought that joins might not be the best way to go.

-Elias

Mark Jaquith wrote:

> On Apr 18, 2007, at 5:04 PM, Matt Mullenweg wrote:
>
>> Ultimately speed is going to be a big factor in any implementation, I
>> hope that the 3 additional tables and related queries don't hit us too
>> hard on the performance side.
>
> If the API for updating/adding/removing tags and post categories is
> solid (i.e. we won't expect people to have a reason to circumvent it),
> we can cache cats/tags in postmeta whenever they are updated.  That
> would actually REMOVE a query from the existing system (as postmeta is
> already queried).  The system could be fail-safe, so that if there is no
> appropriate _wp_categories postmeta entry, it can do the big JOIN'd
> query (and at that moment, populate the postmeta cache).  That doesn't
> help us for Tag/Category views (which posts have X taxonomy?), but it
> helps us when querying tags/categories for a given set of posts.
>
> I'd assume that would be faster than doing a three-way JOIN'd query...
> it certainly sped UTW up.
>
> --
> Mark Jaquith
> http://markjaquith.com/
>
> Covered Web Services
> http://coveredwebservices.com/
>
>
> _______________________________________________
> wp-hackers mailing list
> wp-hackers@...
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>

_______________________________________________
wp-hackers mailing list
wp-hackers@...
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Gali-4]

(up up up)
I'm working on the term_taxonomy table... here is one thought

2007/4/16, Ryan Boren <ryan@...>:

> // term_taxonomy puts a term in the context of a taxonomy (link
> category, post category, or tag).  Hierarchy is put here as well as
> counts.
> CREATE TABLE $wpdb->term_taxonomy (
>  term_id bigint(20) NOT NULL default '0',
>  taxonomy varchar(20) NOT NULL default '0',
>  term_description longtext NOT NULL,
>  parent bigint(20) NOT NULL default '0',
>  count bigint(20) NOT NULL default '0'
> );
> "taxonomy" is a string that can be 'post-category', 'link-category',
> or 'post-tag'.  Plugins can add their own taxonomy types.  This could
> be an id that relates to a taxonomy table instead of a stand-alone
> string, but another table doesn't seem necessary. Another table would
> mean having the taxonomy string in one place rather than two, but I
> didn't drink that much of the normalization punch back in school.

Isn't the description of the "taxonomy" field a definition for a "ENUM" field ?
Optimisation of disk usage, consistency, and still allows a plugin to
add a permitted value to the ENUM list.
And not as normalized as an external table.

My 2 cents.

G
_______________________________________________
wp-hackers mailing list
wp-hackers@...
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Lloyd Budd-2]

On Nov 18, 2007 4:17 AM, Gali <galeenet@...> wrote:
> (up up up)
> I'm working on the term_taxonomy table... here is one thought
>
>
> Isn't the description of the "taxonomy" field a definition for a "ENUM" field ?
> Optimisation of disk usage, consistency, and still allows a plugin to
> add a permitted value to the ENUM list.
> And not as normalized as an external table.

I don't know what I'm talking about, but smart sounding people seem
generally afraid of ENUMs because to change them you have to alter
table, which triggers a table rebuild.

Hope that helps,
--
Lloyd Budd | Digital Entomologist | | Skype:foolswisdom
WordPress.com | WordPress.org | Automattic.com
_______________________________________________
wp-hackers mailing list
wp-hackers@...
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Robin Adrianse-2]

I believe Lloyd is correct, and the only places I would use an ENUM field
would be a field where I know for certain that now and down the road it's
always going to be a set number of possible values, such as a true/false
field.

On Nov 19, 2007 5:40 PM, Lloyd Budd <lloydomattic@...> wrote:

> On Nov 18, 2007 4:17 AM, Gali <galeenet@...> wrote:
> > (up up up)
> > I'm working on the term_taxonomy table... here is one thought
> >
> >
> > Isn't the description of the "taxonomy" field a definition for a "ENUM"
> field ?
> > Optimisation of disk usage, consistency, and still allows a plugin to
> > add a permitted value to the ENUM list.
> > And not as normalized as an external table.
>
> I don't know what I'm talking about, but smart sounding people seem
> generally afraid of ENUMs because to change them you have to alter
> table, which triggers a table rebuild.
>
> Hope that helps,
> --
> Lloyd Budd | Digital Entomologist | | Skype:foolswisdom
> WordPress.com | WordPress.org | Automattic.com
> _______________________________________________
> wp-hackers mailing list
> wp-hackers@...
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>

_______________________________________________
wp-hackers mailing list
wp-hackers@...
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Gali-4]

> On Nov 19, 2007 5:40 PM, Lloyd Budd <lloydomattic@...> wrote:
> > I don't know what I'm talking about, but smart sounding people seem
> > generally afraid of ENUMs because to change them you have to alter
> > table, which triggers a table rebuild.

Do I sound like smart ? :)
ok, the term_taxonomy is a small one, I guess there is no worry about
table size here.

G
_______________________________________________
wp-hackers mailing list
wp-hackers@...
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Lloyd Budd-2]

On Nov 19, 2007 11:44 PM, Gali <galeenet@...> wrote:
> > On Nov 19, 2007 5:40 PM, Lloyd Budd <lloydomattic@...> wrote:
> > > I don't know what I'm talking about, but smart sounding people seem
> > > generally afraid of ENUMs because to change them you have to alter
> > > table, which triggers a table rebuild.
>
> Do I sound like smart ? :)
> ok, the term_taxonomy is a small one, I guess there is no worry about
> table size here.

1.8 million small tables on WordPress.com .
_______________________________________________
wp-hackers mailing list
wp-hackers@...
http://lists.automattic.com/mailman/listinfo/wp-hackers