Scheming refactoring in 1.6

[Andrew Eddie]

This is a thread starter for talking about any schema refactoring in
1.6. Much has already been done but there is still a lot to do. Two
main issues would appear to be:

1. Establishing standards for field types
2. Table and field name standardisation

There are, of course, some other things like moving to InnoDB, etc, so
just bring them up at appropriate times.

Begin :)

Regards,
Andrew Eddie
http://www.theartofjoomla.com - the art of becoming a Joomla developer

[dukeofgaming]

Some ideas that came into mind while making the diagrams for 1.5 and 1.6:

1)Field types:

    - Primary keys: unsigned int, not null and autoincremental with no default

    - Foreign keys: same as primary keys but if nullable then default is null, if not nullable then no default should be given (there might be some serious code refactoring needed here though, not sure, but this is correct for referential integrity)

2)Table and field names:

    - The main primary key should always be called "id" and be located as first column, most tables have it, some omissions would be messages.message_id, banner.bid, bannerclient.cid 

    - Foreign keys should be called as the table they refference plus the suffix "_id", e.g.
access_rules.section_id  and an omission would be banner.cid

    - Recursive foreign keys should be called parent_id. An exception would be if the key has different semantics than a hierarchical structure (i.e. pointer_id, last_revision_id).
    - Foreign keys should always be declared (even though they have no effect in MyISAM, which is excelent for now since no code refactoring would be needed and they would be vital for a future ORM implementation)

    - Table names should be in plural form

    - Pivot tables should be called after the tables they join, e.g. users_sessions would be a pivot between the users and sessions tables

What do you think?

[Andrew Eddie]

I think that's pretty darn close.

The only change I'd strongly suggest making is with the null values in
FK's. Null actually takes some extra space to store (because null is
not zero). I have no issue with storing, for example a category_id as
0. Also means we don't have to fix up the input from a form (that is,
translate exactly an empty string or some horrible magic value to a
true null).

And one suggestion would be to make PK's match the FK's.

And I just realised the subject should have ready "Schema" not
"Scheming" - can't get good help these days I'm afraid.

Regards,
Andrew Eddie
http://www.theartofjoomla.com - the art of becoming a Joomla developer

2009/11/1 dukeofgaming <dukeof...@gmail.com>:

[Sam Moffatt]

As I've noted else where my preference isn't for 'id' named PK's
because it gets potentially ugly when doing table joins - having the
table name suffixed with '_id' as the PK makes it clear when you're
doing a join that you're using the right fields and also prevents
silly issues if someone restructures the queries. Overall it removes
any ambiguity, adds semantic meaning and gives you a simple rule to
follow for key fields. PK == FK instead of FK = tablename + '_' + PK.
It may seem pointless and completely obvious when you're doing simple
joins but it becomes very handy when you end up having to do more
complicated joins and subqueries.

Sam Moffatt
http://pasamio.id.au

[Rafael Diaz-Tushman]

+1 for product_id as opposed to id
+1 for all tables being pluralized

As for xref tables, I've always been partial to either
'users2sessions' or 'usersessionxref'

Cheers,
Rafael

---

[Amy Stephen]

Excellent set of rules.

On the foreign key, I prefer NULL. Any value implies a relationship. But if zero is allowed, it should consistently be used as default for all foreign keys. NULL is technically correct.

Pivot tables are (hopefully) 1:M and the name might better reflect that relationship as singular, then plural.  user_sessions better describes the subject of the table: the sessions (that belong to a specific user.)

I could go either way with the primary key question. From a strict data modeling perspective, no two distinct data elements should have the same name. I also agree with Sam that joins using aliases can get confusing (and potentially introduce bugs). On the other hand, it sure is quick to type id and it does make it clear which table contains the primary key and which contains the foreign key.

Glad to see this scheming about schema underway.

[Niels Braczek]

Amy Stephen schrieb:

> On the foreign key, I prefer NULL. Any value implies a relationship. But if
> zero is allowed, it should consistently be used as default for all foreign
> keys. NULL is technically correct.

I'd prefer NULL, too. Disk space is not a problem nowadays. In some
cases I've been using 0 as a valid id. A user record with ID=0 for
example could contain general settings for guests.

> Pivot tables are (hopefully) 1:M and the name might better reflect that
> relationship as singular, then plural. user_sessions better describes the

> subject of the table: the *sessions* (that belong to a specific user.)

The multiplicity of a relation should be reflected by the table name:
1:1 - singular1_singular2_map
1:n - singular1_plural2_map
n:m - plural1_plural2_map

The suffix '_map' (or '_xref') makes it easier to distinguish maps from
3PD component related tables.
Additionally, there should be a convention for component specific prefixes.

> I could go either way with the primary key question. From a strict data
> modeling perspective, no two distinct data elements should have the same
> name. I also agree with Sam that joins using aliases can get confusing (and

> potentially introduce bugs). On the other hand, it sure is quick to type *id
> * and it does make it clear which table contains the primary key and which
> contains the foreign key.

I don't understand, why

SELECT ... FROM product
... JOIN attribute ON attribute.product_id=product.product_id

is considered to be less confusing than

SELECT ... FROM product
... JOIN attribute ON attribute.product_id=product.id

As you say, Amy, the latter one has the benefit to show, that product is
the master, while attribute contains details. This intuitivity is
somehow lost in the first example.

Regards,
Niels

[Stian Didriksen]

+1 for product_id
+1 for plural table names.
These conventions have been used in the Nooku Framework for a while,
and I can say it works great.
Especially when you add inflectors in to the mix and write more
intelligent code.
Best example I can give on that is how Nooku fw automatically look for
the 'harbour_boats' table in a URI like this: index.php?
option=com_harbour&view=boats
and push a list object with 'boats' to the view.

That's just one of many examples were these conventions are vital.

I also think moving to InnoDB would be very benefitial.
The cons of InnoDB over MyISAM, like slower SELECT queries, isn't as
much of a problem as you can always write more efficient queries for
that matter.
However, the fact that MyISAM don't have foreign keys, transactions
and more, is not something that you can as easily work around somehow.

That's my opinion on it for what it's worth :)

[dukeofgaming]

Exactly Niels, the "ambiguity" of id gets easily dissipated with table aliases (allowing the programmer to even use "banners as b ... b.id" if she or he wants); I like to see the table name as some kind of namespace, and my SQL queries always carry table names/aliases to make stuff clearer and more maintainable (no order implied, never an ambiguity). I also have the habit of using the following three fields in all tables: id (int), date (int) and status (tinyint), and I never have problem identifying which belongs to who under this convention.

My impression of why some like having their fk == other table's pk is just saving the table alias, but this is sometimes silly and even more redundant as Niels pointed out ( i.e. "product.product_id"), and as queries grow most of the times you end up using table aliases.

And well, most of the pks are "id" already =P, there are just like 6 out of 39 tables where its not.

About null taking space, it DOES take space in MyISAM (it doesn't take in InnoDB), but the main reason is what Amy said about not implying a relationship. Now, about having to fix the input of a form (what Andrew said), I think this could be quickly checked like this:

$some_field = JRequest::getVar('product_id') or $some_field = null;// works for when JRequest::getVar returns falsy values such as 0, "0" or ""

...or when we have PHP 5.3 as min req 

$some_field = JRequest::getVar('product_id')?:null;

About the pivot tables & multiplicity, what Niels says is better; even though I am not so shure "_map" is really necessary, it is already being used in several places.

Even if we do not support InnoDB as default, being compatible with it would enforce correctness in the structure of the database (such as the nullable stuff and having actual fks).

How do you guys feel about actually declaring all those foreign keys?. I can make the addition in a sneeze with MySQL Workbench. If you guys want to see the diagrams, they are available here: http://rapidshare.com/files/299365808/j16migration_diagrams.rar

Regards,

David

[Andrew Eddie]

> About null taking space, it DOES take space in MyISAM (it doesn't take in
> InnoDB), but the main reason is what Amy said about not implying a
> relationship. Now, about having to fix the input of a form (what Andrew
> said), I think this could be quickly checked like this:
> $some_field = JRequest::getVar('product_id') or $some_field = null;// works
> for when JRequest::getVar returns falsy values such as 0, "0" or ""
> ...or when we have PHP 5.3 as min req
> $some_field = JRequest::getVar('product_id')?:null;

You can get JForm to handle it with an attribute in the XML
definition. But, really, I know it *is* technically correct from a
data model standpoint, but in reality does accounting for null
actually add any value over using zero in that majority of cases? If
you want to handle default values, use a real, reserved number (like
1). In PHP land you then miss out on simple checks like empty() as
opposed to doing a triple check on null, then zero, then a positive
value. Another way of putting it is Joomla currently works happily on
NOT NULL FK's - what is the value to me, the developer, that I can
pass on to the user by using null FK's?

If it makes sense, let's do it, but I'm erring on the side that I'm
not convinced it's worth the trouble.

BTW, this is the advice I was given during MySQL training by one of
the guys that wrote MySQL. Admittedly that was only his opinion, but
hey :)

On cross-reference tables I could certainly go with:

1 to N: jos_user_sessions
M to N: jos_users_usergroups

That makes sense to me.

Another thing to add is a 3PD should have an extra namespace to avoid
collisions with other extensions.

David, do you want to start a wiki page to collect the thoughts as we go?

[dukeofgaming]

I was thinking on using this wiki page: http://docs.joomla.org/Coding_style_and_standards#Naming_Conventions

(btw, I think this is a duplicate?: http://docs.joomla.org/Naming_conventions)

About the triple check, maybe we could rule out zero checking and positive checking if we just standardize PKs data types to unsigned int (always positive) autoincremental (always 1 or greater)... wait, that is already done in most cases from what I saw =P, and well this should be the same convention for foreign key fields, wouldn't you say? (actually, it would throw error #150 because of type mismatch when declaring a fk).

Regarding the value to pass to the user I always use empty string since its the most "valueless" thing I can present. When working with javascript and json there is no problem using null.

I also think the namespaces for the 3PDs is the way to go (instead of "_map" I presume, since there is a user_usergroup_map table).

Regards,

David

[Andrew Eddie]

I'd create a new page, say:

http://docs.joomla.org/Database_schema_standards

??

Regards,
Andrew Eddie
http://www.theartofjoomla.com - the art of becoming a Joomla developer

2009/11/2 dukeofgaming <dukeof...@gmail.com>:

[dukeofgaming]

Sure, that would be great =)

[Andrew Eddie]

Done.

http://docs.joomla.org/Database_schema_standards

Knock yourself out :)

[dukeofgaming]

Thanks Andrew, any chance you could lower the edit rights to the autoconfirmed wiki group?.

[Andrew Eddie]

No idea how to do that sorry. I think one of the wiki-masters will have to.

[Chris Davenport]

Eh?  You have write access as soon as you confirm your email address.

Chris.

2009/11/2 dukeofgaming <dukeof...@gmail.com>

[elin]

For fields, could we standardize on either "published" or "state" and
on either "title' or "name"?

I still think it would be helpful to make the fields more consistent
across components (e.g. add publish_up and publish_down, consistent
meta data fields).

Elin

On Nov 2, 3:12 am, Chris Davenport <chris.davenp...@joomla.org> wrote:
> Eh? You have write access as soon as you confirm your email address.
>
> Chris.
>

> 2009/11/2 dukeofgaming <dukeofgam...@gmail.com>

>
> > Thanks Andrew, any chance you could lower the edit rights to the
> > autoconfirmed wiki group?.
>

> > On Sun, Nov 1, 2009 at 7:23 PM, Andrew Eddie <mambob...@gmail.com> wrote:
>
> >> Done.
>
> >>http://docs.joomla.org/Database_schema_standards
>
> >> Knock yourself out :)
>
> >> Regards,
> >> Andrew Eddie

> >>http://www.theartofjoomla.com- the art of becoming a Joomla developer
>
> >> 2009/11/2 dukeofgaming <dukeofgam...@gmail.com>:

> >> > Sure, that would be great =)
>

> >> > On Sun, Nov 1, 2009 at 6:15 PM, Andrew Eddie <mambob...@gmail.com>

> >> wrote:
>
> >> >> I'd create a new page, say:
>
> >> >>http://docs.joomla.org/Database_schema_standards
>
> >> >> ??
>
> >> >> Regards,
> >> >> Andrew Eddie

> >> >>http://www.theartofjoomla.com- the art of becoming a Joomla developer
>
> >> >> 2009/11/2 dukeofgaming <dukeofgam...@gmail.com>:

> >> >> > On Sun, Nov 1, 2009 at 3:15 PM, Andrew Eddie <mambob...@gmail.com>

> >> >> >>http://www.theartofjoomla.com- the art of becoming a Joomla
> >> developer

[dukeofgaming]

Sorry Chris, thought I already had done that, just fixed it.

I think what Elin suggests is a very nice idea, I would go for "state" (or perhaps "status") and "name".

[Rafael Diaz-Tushman]

On this issue, I prefer a boolean field `enabled` (as opposed to `published`) b/c `enabled` is already abstracted as it is (whereas `published` seems specific to articles, books, newspapers & such) with an `enabled_date` and `disabled_date` (along with `modified_date` and `created_date`)

+1 for `name` fields.

`state` I personally feel is also good, but may be reserved for specific cases where additional states are created for the extension & managed by another db table, making `state` more like `state_id` that refers to the other table...

Just my $0.02.

Cheers!
--
Rafael Diaz-Tushman, President & CEO
Dioscouri Design: Form and Function
www.dioscouri.com
www.twitter.com/dioscouri

[elin]

I think we use enabled/disabled to refer to broader functionality such
as whether a module or plugin is available.

State/publish refers to content (not just com_content, but items that
will be rendered by components). Currently state/published has these
possible values depending on which component you look at:

published
unpublished
archived
trashed
approved (weblinks only)
reported (weblinks only)

Additionally published can be "published but not yet visible because
not at publish_up date yet" and "published but no longer visible
because past publish_down date."

I think that reported be handled separately from state, since an
administrator may choose to automatically unpublish reported items or
not to. I think that reported is going to become a lot more important
with comments and acl. Approved also seems to me more like a workflow
status than a state.

Elin

On Nov 2, 3:15 pm, Rafael Diaz-Tushman <rdiaztush...@dioscouri.com>
wrote:

> On this issue, I prefer a boolean field `enabled` (as opposed to
> `published`) b/c `enabled` is already abstracted as it is (whereas
> `published` seems specific to articles, books, newspapers & such) with an
> `enabled_date` and `disabled_date` (along with `modified_date` and
> `created_date`)
>
> +1 for `name` fields.
>
> `state` I personally feel is also good, but may be reserved for specific
> cases where additional states are created for the extension & managed by
> another db table, making `state` more like `state_id` that refers to the
> other table...
>
> Just my $0.02.
>
> Cheers!
> --
> Rafael Diaz-Tushman, President & CEO
> Dioscouri Design: Form and Functionwww.dioscouri.comwww.twitter.com/dioscouri
>

> On Mon, Nov 2, 2009 at 2:18 PM, dukeofgaming <dukeofgam...@gmail.com> wrote:
> > Sorry Chris, thought I already had done that, just fixed it.
>
> > I think what Elin suggests is a very nice idea, I would go for "state" (or
> > perhaps "status") and "name".
>

> > On Mon, Nov 2, 2009 at 12:53 PM, elin <elin.war...@gmail.com> wrote:
>
> >> For fields, could we standardize on either "published" or "state" and
> >> on either "title' or "name"?
>
> >> I still think it would be helpful to make the fields more consistent
> >> across components (e.g. add publish_up and publish_down, consistent
> >> meta data fields).
>
> >> Elin
>
> >> On Nov 2, 3:12 am, Chris Davenport <chris.davenp...@joomla.org> wrote:
> >> > Eh?  You have write access as soon as you confirm your email address.
>
> >> > Chris.
>
> >> > 2009/11/2 dukeofgaming <dukeofgam...@gmail.com>
>
> >> > > Thanks Andrew, any chance you could lower the edit rights to the
> >> > > autoconfirmed wiki group?.
>
> >> > > On Sun, Nov 1, 2009 at 7:23 PM, Andrew Eddie <mambob...@gmail.com>
> >> wrote:
>
> >> > >> Done.
>
> >> > >>http://docs.joomla.org/Database_schema_standards
>
> >> > >> Knock yourself out :)
>
> >> > >> Regards,
> >> > >> Andrew Eddie

> >> > >>http://www.theartofjoomla.com-the art of becoming a Joomla developer

>
> >> > >> 2009/11/2 dukeofgaming <dukeofgam...@gmail.com>:
> >> > >> > Sure, that would be great =)
>
> >> > >> > On Sun, Nov 1, 2009 at 6:15 PM, Andrew Eddie <mambob...@gmail.com>
> >> > >> wrote:
>
> >> > >> >> I'd create a new page, say:
>
> >> > >> >>http://docs.joomla.org/Database_schema_standards
>
> >> > >> >> ??
>
> >> > >> >> Regards,
> >> > >> >> Andrew Eddie

> >> > >> >>http://www.theartofjoomla.com-the art of becoming a Joomla

> >> > >> >> >>http://www.theartofjoomla.com-the art of becoming a Joomla
> >> > >> developer

[Andrew Eddie]

"status" I like. I think in context publish_up and _down are ok, but
maybe publish_start_date, publish_end_date is a bit better.
Contextually we are talking about only the published state of the
content (cf, status_state_date means nothing).

Generally we use "name" as a field to mean a programatic name that the
API relies on. "title" is generally used to refer to the displayed
title of the content, so my preference would be to stick with "title"
and mop up any odd usage of "name".

Regards,
Andrew Eddie
http://www.theartofjoomla.com - the art of becoming a Joomla developer




2009/11/3 dukeofgaming <dukeof...@gmail.com>:

[Amy Stephen]

To me published means available for distribution which, in my thinking, is a very distinct meaning from enabled, which to me describes a technical functionality that, when enabled, is "capable of doing the action it was intended to do."

• The Article Manager is enabled or disabled.
• Articles are published or not published.

To me, state makes sense as a workflow indicator, where content is: created, edited, published, unpublished, archived, deleted, (and then trashed.). That would be state to me.

Definitely agree on publish_start_date and publish_end_date, rather than publish_up and publish_down.

I wish we would also use the publish_start_date for printing, rather than create_date which isn't relevant from a publishing perspective.

Would recommend continuing with name for users and contacts.

I assume this is a discussion for 1.7, rather than 1.6? These types of changes the Release Team indicated would wait, earlier. Is that still the case?

[Rafael Diaz-Tushman]

Out of frustration with A.J. Burnett (a 3R HR in the 1st inning will do that...), I'm distracting myself at the computer, so this is a good time to chime in again.  

I think we're in agreement with `enabled` vs `published`.   When Elin first commented, I thought she was talking about standardizing across core extensions, not just with com_content, which is why I mentioned using `enabled` rather than `published`.  If she was just talking about com_content, then I agree with `published` (and derivatives, like publish_date, unpublish_date) -- like I said, `published` seems to apply to articles, books, newspapers, etc, and therefore makes sense for com_content (and newsfeeds, for example).

However, we're using `published` for

_categories

_contact_details

_languages

_menu

_modules

_redirect_links

and I suspect these instances of `published` are simply the result of mirroring com_content (i.e. treating the entire Joomla framework as content-related rather than abstracting it).  Do you `publish` a language in a system, or do you `enable` it?  How about a redirect link?

If it doesn't matter to the group (and I'm not even convinced it's worth the trouble of changing) then we should just stick with `published` as the de facto standard across nearly all the core components.  I just wanted to share what I thought.

Ok, back to the game :-)

Cheers!

--
Rafael Diaz-Tushman, President & CEO
Dioscouri Design: Form and Function
www.dioscouri.com
www.twitter.com/dioscouri

[elin]

See I think it's totally fine to think of a contact as published. It
just means that it's available to users, just like publishing on paper
(which doesn't necessarily have to be a book or magazine, could be a
contact list or anything else).

Name is used for contacts and users, i.e. it is what is displayed in
the places in the grids where we have lists of those things as well as
for the page titles (subject to all the rules for those) and using
both name and title makes reusability of the grids, layouts and
queries more of a pain. Yes, I think it would be better to call the
fields title in the database even if we use the string name in the
layouts.

Elin

On Nov 2, 8:39 pm, Rafael Diaz-Tushman <rdiaztush...@dioscouri.com>
wrote:

> On Mon, Nov 2, 2009 at 8:14 PM, Amy Stephen <amystep...@gmail.com> wrote:
> > To me *published* means available for distribution which, in my thinking,

> > is a very distinct meaning from enabled, which to me describes a technical
> > functionality that, when enabled, is "capable of doing the action it was
> > intended to do."
>

> >    - The Article Manager is enabled or disabled.
> >    - Articles are published or not published.

>
> > To me, state makes sense as a workflow indicator, where content is:
> > created, edited, published, unpublished, archived, deleted, (and then
> > trashed.). That would be state to me.
>
> > Definitely agree on publish_start_date and publish_end_date, rather than
> > publish_up and publish_down.
>
> > I wish we would also use the publish_start_date for printing, rather than
> > create_date which isn't relevant from a publishing perspective.
>
> > Would recommend continuing with name for users and contacts.
>
> > I assume this is a discussion for 1.7, rather than 1.6? These types of
> > changes the Release Team indicated would wait, earlier. Is that still the
> > case?
>

> > On Mon, Nov 2, 2009 at 5:13 PM, Andrew Eddie <mambob...@gmail.com> wrote:
>
> >> "status" I like.  I think in context publish_up and _down are ok, but
> >> maybe publish_start_date, publish_end_date is a bit better.
> >> Contextually we are talking about only the published state of the
> >> content (cf, status_state_date means nothing).
>
> >> Generally we use "name" as a field to mean a programatic name that the
> >> API relies on.  "title" is generally used to refer to the displayed
> >> title of the content, so my preference would be to stick with "title"
> >> and mop up any odd usage of "name".
>
> >> Regards,
> >> Andrew Eddie

> >>http://www.theartofjoomla.com- the art of becoming a Joomla developer
>

> >> 2009/11/3 dukeofgaming <dukeofgam...@gmail.com>:

> >> > Sorry Chris, thought I already had done that, just fixed it.
> >> > I think what Elin suggests is a very nice idea, I would go for "state"
> >> (or
> >> > perhaps "status") and "name".

> >> > On Mon, Nov 2, 2009 at 12:53 PM, elin <elin.war...@gmail.com> wrote:
>
> >> >> For fields, could we standardize on either "published" or "state" and
> >> >> on either "title' or "name"?
>
> >> >> I still think it would be helpful to make the fields more consistent
> >> >> across components (e.g. add publish_up and publish_down, consistent
> >> >> meta data fields).
>
> >> >> Elin
>
> >> >> On Nov 2, 3:12 am, Chris Davenport <chris.davenp...@joomla.org> wrote:
> >> >> > Eh?  You have write access as soon as you confirm your email address.
>
> >> >> > Chris.
>
> >> >> > 2009/11/2 dukeofgaming <dukeofgam...@gmail.com>
>
> >> >> > > Thanks Andrew, any chance you could lower the edit rights to the
> >> >> > > autoconfirmed wiki group?.
>
> >> >> > > On Sun, Nov 1, 2009 at 7:23 PM, Andrew Eddie <mambob...@gmail.com>
> >> >> > > wrote:
>
> >> >> > >> Done.
>
> >> >> > >>http://docs.joomla.org/Database_schema_standards
>
> >> >> > >> Knock yourself out :)
>
> >> >> > >> Regards,
> >> >> > >> Andrew Eddie

> >> >> > >>http://www.theartofjoomla.com-the art of becoming a Joomla

> >> developer
>
> >> >> > >> 2009/11/2 dukeofgaming <dukeofgam...@gmail.com>:
> >> >> > >> > Sure, that would be great =)
>
> >> >> > >> > On Sun, Nov 1, 2009 at 6:15 PM, Andrew Eddie <
> >> mambob...@gmail.com>
> >> >> > >> wrote:
>
> >> >> > >> >> I'd create a new page, say:
>
> >> >> > >> >>http://docs.joomla.org/Database_schema_standards
>
> >> >> > >> >> ??
>
> >> >> > >> >> Regards,
> >> >> > >> >> Andrew Eddie

> >> >> > >> >>http://www.theartofjoomla.com-the art of becoming a Joomla

> >> >> > >> >> >>http://www.theartofjoomla.com-the art of becoming a
>

> ...
>
> read more »- Hide quoted text -
>
> - Show quoted text -