On 5 Aug 2018, at 2:40am, Stephen Chrzanowski <pontia...@gmail.com> wrote:
> I'm making a small database for a game, and a bunch of the tables follow
> the same kind of naming convention due to normalization, like
>
> {Name_Of_Information}s
> {Name_Of_Information}ID as Integer
> {Name_Of_Information}Name as Char
>
> So for example:
>
> Resources
> ResourceID as Integer
> ResourceName as Integer
The name of a table should not be variable. SQL has tables with fixed names
and variable contents, and the entire support stack is designed to assist this.
So move the names of your tables, which are variable, into a table.
In the case of the above schema, the fix would be this:
TABLE InfoStore (
infoType TEXT,
ID INTEGER,
name TEXT);
CREATE UNIQUE INDEX IS_InfoType_ID ON InfoStore (infoType, ID);
Assign an IDs for a new 'SolarSystem' row by calculating
1 + max(SELECT ID FROM InfoStore WHERE infoType='SolarSystem')
Everything goes into one big table which has a fixed name. Magically you no
longer need weird things like CTEs, and all access can be done by binding
column values.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
