Hello,
I've mentioned before how I want to look at changing the schema. In
particular, I want to normalise it a bit and, for example, put the
permissions and dynamic permissions into their own table. The node
table is definitely not in 3rd normal form, but I don't think it's in
1st normal form either given that the permissions columns are 'repeating
groups', IMHO. I also don't think it's in 2nd NF because most
permissions values are repeated, this means that they are not dependent
on the primary key, but on something else. In fact, they're dependent
on the nodetype and hence on a relation. Pushing them out into their
own table(s) means that we can also better ensure data consistency.
Speaking of data consistency....
In my last email I mentioned trying to get SQL totally out of nodeballs,
or at least out of ecore... (if nodeball authors want to tie their
nodeballs to one DB, it's none of my business).
I realised that I couldn't do this before writing some tests that test
against a live DB. All our DB tests currently use mocking heavily,
which is OK as far as it goes, but when push comes to shove you need to
know that the database dependent sql you're sending to the DB actually
works.
As, I've been doing this, I've come up against that data consistency
issue again. For example, each row on the group table points to two
rows in the node table. This means that they're foreign keys, yet no
foreign key constraint is enforced. As a result you can get hanging
group table entries....I have two currently on one site, I'm running.
On deletion of the relevant nodes, those rows in the group table should
have been deleted. I don't know why they weren't, but they weren't.
"ON DELETE CASCADE" would not have allowed these hanging foreign keys to
exist. There are more examples of this sort of thing.
I think when Everything is installed on Pg, these constraints should be
enforced. As for MySQL, I think we should install as InnoDB and if
someone wants to convert to MyISAM let them do it, but Everydevel should
aim for some transactional integrity.
So on to nodetypes...
One table that is clearly not normalised is the setting table. Settings
can include literal values that may either be integers or strings or
they may a reference to an entry in the node table (i.e. a foreign
key). That's three datatypes which are two too many for an easily
workable solution.
However, recently, I've tried to think of a way of implementing
blog-style comment threads. The obvious way to do this would be to make
each comment a 'node'. However, it didn't feel right and the reason was
because the philosophy behind a node is that the author has some
ownership over it and can edit it. Blog-style comments don't have this
attribute. I then looked at one of the comment modules on Drupal did.
The solution seems to be to have a 'comment thread' nodetype. Any node,
can have a 'comment thread' attached to it. We don't know how long a
comment thread is so it's an aggregation of a list of data. Actually,
my comment thread is reasonably complex as I have three tables. One is
the comment itself, one is the poster who is identified by his/her email
and the third is a blacklist of IP addresses from which no posting is
allowed.
Which brings me back to settings. Settings are lists of data values, so
they are a list type or a subclass of list. Don't you think?
Paul
|
