dupicates check

[Fou-Lu]

This is pretty much exactly what I was thinking as well, minus the staging table. I can see your point on this one, but treating it as staging then truncating it is a good idea.

"Tombstone" records are IMO the right approach, where tombstoning it is based on whatever criteria you want. Users sessions expire if they've had no activity for 15 minutes, a article is old if its > 3 weeks, and in this case a record is inactive if isCurrentlyActive is 0 (does MySQL actually support boolean yet?). So its simply a matter of your criteria, and unless diskspace is a huge concern, there's really not a need to delete "expired" records (except for something like non-persisting sessions since you'll never reuse them).

[Old Pedant]

MySQL allows BOOLEAN as an alias for BIT:

CREATE TABLE foo (
    flag BOOLEAN,
    flag2 BIT
);

But if you then use DESCRIBE foo; both fields will show up as BIT(1).

It also allows the keywords TRUE and FALSE:

UPDATE foo SET flag = True WHERE id = 777;

But, again, if you then do SELECT flag FROM foo you will see a 1 or 0 value for the BIT field.

So they really just have 3 aliases in place: BOOLEAN for BIT(1), TRUE for 1, FALSE for 0.

Whoopee.

[Fou-Lu]

Originally Posted by Old Pedant

Whoopee.

Lols
I'm happy with that, I like to use =TRUE and =FALSE if I have the option. Not that storage wise it makes a difference, but it reminds me that it should only be true or false.
Come to think of it I seem to recall that true and false have worked for awhile. MySQL used to use the tinyint though, but I think it was much larger than a single bit even for its smallest size? Not that it matters.

Still, its no different than booleans in PHP :P

[SlayerACC]

At the end of all this I think I am more confused then when I started..

Should I use

Insert ignore statement.


or should I use

UPDATE maintable SET isCurrentlyActive = 0;

INSERT INTO maintable (primaryKeyField, field1, field2, field3, isCurrentlyActive )
SELECT primaryKeyField, field1, field2, field3, 1 FROM stagingtable
ON DUPLICATE KEY UPDATE isCurrentlyActive = 1;

TRUNCATE TABLE stagingTable;

I am lost....I would kill to know this much about this area.


Slayer.

[Old Pedant]

I *think* FouLu is agreeing with me. He wrote

This is pretty much exactly what I was thinking as well, minus the staging table. I can see your point on this one, but treating it as staging then truncating it is a good idea.

So... It's really a variation on your original plan.

Yes, you have a separate table that you import the CSV file into.

But you use your master table to keep *ALL* records *EVER* imported. And you use that flag, isCurrentlyActive (or any other name you prefer, of course), to indicate "live" records. That way, all your prior records are always there. And having all in a single table is *highly* preferable. For example, it means you can search both historical and current data in a single query by simply ignoring th isCurrentlyActive flag.

Do you understand the steps involved in this:

UPDATE maintable SET isCurrentlyActive = 0;

INSERT INTO maintable (primaryKeyField, field1, field2, field3, isCurrentlyActive )
SELECT primaryKeyField, field1, field2, field3, 1 FROM stagingtable
ON DUPLICATE KEY UPDATE isCurrentlyActive = 1;

TRUNCATE TABLE stagingTable;

It's really pretty simple:

First, you import the CSV file into your "staging" table. (That step is not shown above.)

Then, you change *ALL* records in the main table to indicate that they are historic. That is, they are *NOT* currently active.

Now you merge your "staging" records into the main table. But the ON DUPLICATE KEY simply says "if I try to merge a record from staging into the main table *AND* the KEY for that record already exists in the main table, then forget about the merge and instead simply mark the record in the main table as *ACTIVE* again." Doesn't that make sense? You first made all the records inactive and then, on the merge, you "resurrect" any records that are being re-imported.

Once the merge is done, you wipe out all the records in the staging table, ready to do it all again next week.

[SlayerACC]

Thank you guys for taking sooooo much time on this..

I will implement this and see how it all works.


A big thanks to you both.


Slayer.