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
