I've spent the weekend refreshing on databases. I'm using LiveCode and also
Trevor's fabulous sqlYoga, and beginning to realise how little I know about
databases! The question I've got is about the database schema design and
optimising it for the speed of adding records.
* Exporting Handlers*
I
Hi David
However if I uses a
surrogate primary key (ie the usual auto-incremented numeric id field), for
each updated record I need to check first whether it is already in the
database before adding it - this is a lot of overhead in terms of finding
the ids based on other fields.
I haven't
HI Monte - hows down-under :)
On 27 September 2010 11:31, Monte Goulding mo...@sweattechnologies.comwrote:
I haven't yet used SQLYoga but I'm sure Trevor has this all worked out.
SQLite will handle the auto increment of integer primary keys when you
INSERT without an ID field and then you
HI Monte - hows down-under :)
Getting better all the time ;-)
Only by making the primary key = name,type,location,hnum instead of id
woudl you get a fast normalisation of the data inserted.
You can create an secondary key for those fields and still use your primary key
auto-increment field
OK - not getting very far with that - Google is not my friend :)
Can you give me an idea of what the CREATE statement would look like?
CREATE TABLE 'handler' (
'name' VARCHAR(255) NOT NULL,
'type' VARCHAR(255) NOT NULL,
'location' VARCHAR(255) NOT NULL,
'handler_num' INTEGER
Try:
-- -
-- Table `handler`
-- -
CREATE TABLE IF NOT EXISTS `handler` (
`id` INT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(255) NOT NULL ,
`type` VARCHAR(255) NOT NULL ,
`location`
Thanks for that Monte,
On 27 September 2010 13:44, Monte Goulding mo...@sweattechnologies.comwrote:
UNIQUE INDEX `mykey` (`name`, `type`, `location`, `handler_num`) );
Or you can use a separate Create Unique Index statement.
OK - will have to read up more on Indexes - I thought they were
