sqlite supports 'natural join' which allows you to omit the on clause for well structured databases.
My database model is really a C# DataSet ( which contains DataTable, which contain DataColumn and DataRow, (and this is superfluous, but row has a reference to it's table and therefore the columns to relate to the array of elements it contains) DataRelations are in DataSets between DataTables, and they are foriegn key definitions; with OnUpdate, OnDelete, et al behaviors definable. I have a module that's open source that takes a data table and many common operators and generates create table and constratint thigns for DataSets. The DataSet CAN be designed using the dataset designer (it's OK; nothing that you couldn't find better of in the 90's). table1 table1 + _id --automatic primary key; I use object in the datatable now as the type so the key can be GUID or int autoincrement. table1 + _name -- common text field that this ID might be printable in part as... could be _description, _text, _ToString ? table2 table2 + _id table1 + _id DataRelation( table2, table1.TableName+"_id", table1, table2.TableName ).onDelete = (?rule enum).Cascade. *shrug* too specific I know which can be wrapped in something ilke makeDataRelation( table2, table1 ); automatically and consistently. The problem becomes self-recursive keys which should themselves have just tablename_id, but would conflict with themselves... map1 map1_id int auto_increment PRIMARY KEY, parent_map1_id int node_info_id int where it joins ( parent_map1_id = map1_id ) ... --------------- I did learn in school something like how to make words plural.... public static string StripPlural( string s ) { if ( ( s.Length > 3 ) && String.Compare( s, s.Length - 3, "ies", 0, 3 ) == 0 ) return s.Substring( 0, s.Length - 3 ) + "y"; if ( ( s.Length > 3 ) && String.Compare( s, s.Length - 3, "ses", 0, 3 ) == 0 ) return s.Substring( 0, s.Length - 3 ) + "s"; if ( ( s.Length > 1 ) && s[s.Length - 1] == 's' ) return s.Substring( 0, s.Length - 1 ); return s; } So I did add rules so I could take a plural table name like "Players" and make Player_id, Player_name, etc... And many tables had an ID that was an _info record or a _decription of something and "member_info" to be member_id, etc... public static string StripInfo( string s ) { int trim = s.IndexOf( "_info" ); if( trim > 0 ) return s.Substring( 0, trim ); trim = s.IndexOf( "_description" ); if( trim > 0 ) return s.Substring( 0, trim ); return s; } We did try to suffix tables with sort of a data type; kind of the table name is it's object-type.... maybe there's no justification for chopping it, and in correctness the full tablename shoudl be used so players and player_info and player_desc don't all collide at some point; I could blame it on having to conform to an existing system? The new system was certainly easy to automate and even work with by hand. prefixes can be more useful.... a DataSet can have a prefix that gets applied to all tables in it, so if you had a small cluster of information like user_permissions, which has a grouping of tables, they could all share a similar prefix and be located together when browsing. (there are no relations between datasets, but table names would never include their prefix when referencing them... ) Please do feel free to rip giant holes in anything I've said :) _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users