Sean Utt wrote:
And then what? Make the search box on able to handle an email address as search text without throwing a shoe?

Search for [EMAIL PROTECTED] or any other 'email' address from the postgres home page. Barfage every time. Easy for some isn't easy for all, apparently. Left that out as a test case did we? Someone searching a mailing list for an email address? Who wudda thunk it? It works without the . -- don't know why, but then I also don't know why someone hasn't tried that before me.

Had a bad day?

And I'll be damned if you're not right - it doesn't return results for but does for [EMAIL PROTECTED] Presumably something to do with (not ?) splitting the email address on ".". Can't believe no-one has noticed this before (me, for example). I guess that even though I search a lot, it's not on email addrs.

Have you reported it to the www team?

"Sure, sounds like a simple solution to me..." Richard said sarcastically. Would be funnier if the search on the website wasn't broken in a completely stupid, almost ironic way. Ah, irony and sarcasm -- the ugly twins.

Actually, it was Greg who said that. And it was *me* the (really very gentle) sarcasm was directed at.

Yeah, we have to dynamically generate queries day in and day out. But then some of us actually work for a living.

Umm - like all of us?

Since we already have to do that, maybe someone could make that easier?

Good idea!

Isn't that really the point here?

Not as I was reading the discussion.

> Someone asked if something would be
useful, and the people who use the database to do real work said YES, and here's how I might use it. Like full text seach and recursive queries, user defined (fields|attributes|properties) and the ability to manage them would be BUTTER! Is it a difficult problem? YES, but if it wasn't, why should it be worth an advanced degree?

I think the main discussion has been around:
1. Whether Edward's final-year project is basically EAV (in which case he'll probably need to work hard to get good marks). 2. Whether dynamically generating DDL is safe/practical in a business setting. This seems to split some *very* experienced people about 50:50. Half of them agree with me and the other half are inexplicably wrong ;-)

If you read section 0.3 of Edward's project proposal he argues that dynamic DDL causes problems for the application because: "However, SQL does not provide an easy syntax for querying these properties." (meaning the changed structure of the database). I'm not saying this part is easy, but I'm not convinced it's harder than doing it the "wrong" way. At least not if you do as Edward does and enforce types.

Now, in 0.3.1 he describes a normalised webpage=>tags table pair and shows some cumbersome-looking SQL. However, I'd argue this is due to the verbose nature of the SQL rather than the underlying expressions.

He then looks at what I think of as the "system settings table"* problem, where you have a bunch of configuration-settings you'd tend to store in a single table (setting_name, value), except you'd like to have different types for each setting (a further wrinkle is that you might like lists of settings - do you use arrays or a sub-table?). This is your classic EAV scenario.

Now, he proposes a set of tables - one per property, so you can enforce type constraints, but he will need to create syntax to make this easier to query. Presumably it'll need an automatically-generated view over the top. (Edward - tip: read up on Date's thoughts on automatically determining what table you can insert a row in based on its type).

This certainly looks like a variant on EAV to me, and I'm not convinced that it's gaining much since you'll have possibly huge numbers of joins going on in the background while not having any semantics to your table definitions.

The other variant (which I've used myself) is to have a "type" column and a trigger to enforce the text-value is valid for "type".

Now, if you do it "properly", that is define tables as you would as a human designer, then you do have the problem of informing your application on the relevant structure. Now, the syntax issues of this are largely solved - plenty of ActiveRecord-style classes out there for Ruby,Python,Perl,PHP,...

The interesting question then becomes where the practical limits in such flexibility are. Simple attribute/value pairs are easy enough. How do you feel about changes to existing tables though? How about new tables that add relationships between existing tables? At what point does the application just become pgAdmin?

* Another good example is the "questionnaire". Users need to be able to define their own lists of questions and if "driving_licence=no" then don't bother to ask "type of car=?". Edward - if you want to see a schema that implements this sort of thing, contact me off list and I'll see what I can do. The client for that project will probably be fine with sharing it with one student.

  Richard Huxton
  Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [EMAIL PROTECTED] so that your
      message can get through to the mailing list cleanly

Reply via email to