Hokay, I'm just trying to create a solid database structure and it is turing out to be a real ass.

I notice that Links SQL and GForum table structures do not specify FOREIGN KEY ... (...) or REFERENCES (...) in any of the create table commands and it is all done via the defs....I'm wondering what the reason for this is.....is it just flexibility or compatibility or?

I really like the idea of InnoDB tables handling foreign key contraints automatically. I'm guessing that due to compatibility, this is why GT products handle the keys via def files. Is that about right?

Is using def files the way to go for now until key constraints are handled automatically by the sql server?

Looking through GT::SQL it is quite a task to handle everything.

In terms of referential integrity and constraints, they are created differently (in terms of syntax) across different database applications, so compatibility is probably the primary factor for not defining constraints within the database structure.

If you are programming something specific to one database application server, like MySQL, then you could be more stringent in your constraint definition in the back-end/database structure. But if you are programming for multiple database application servers, like Oracle, Postgres, MS SQL, IBM, etc., then you'd need to basically define your constraints in user-friendly manner within your definition files rather than in the database structure via configuration/install scripts.
========================================
Buh Bye!

Cheers,
Me

Thanks. I'm also finding that creating indexes is a real task too.

I guess you have to base the indexes on the type of queries you are using but getting them all maximized performance wise is rather tricky.

You're welcome.

Indexing has a lot to do with the "end result logic", meaning how the data will be used ultimately...if you are programming an interface to the database application, then making that as flexible as possible is "key" (no pun intended).
========================================
Buh Bye!

Cheers,
Me

Hi,

That was due to MySQL's lack of foreign key support (and I've yet to see an isp or client running innodb tables). We wanted the feature and implemented it into GT::SQL. We also parse it out to automate the joins (i.e. so when you do a select between multiple tables, it knows the keys to join it on).

Cheers,

Alex
--
Gossamer Threads Inc.

Definitely. It's something that really needs a lot of thought behind. For instance, look at the Post table in Gossamer Forum. We need to optimize it for several types of selects. i.e. when viewing a forum, we want to display root level posts in chronological order by last poster. So we add an index on post_root_id, forum_id_fk, post_latest_reply. A little background on how multiple keys work, and some practice with mysql's EXPLAIN will help you see when MySQL uses an index and when it doesn't.

Cheers,

Alex
--
Gossamer Threads Inc.

Yeah I peeked at the posts table earlier today which set my heart racing :)

I've been deep in the mysql docs and it seems that the indexes need to be set properly to maximize JOIN efficiency and also for SELECT'ing with the best efficiency. Not to mention making sure you define multiple indexes properly for WHERE clauses as well as ordering them properly....BAH!