How many DB tables make up a single Character Definition?

[picaro11]

Just curious.

I imagine a CHARACTER_ID in some DB Table has foreign key constraints on many other tables that make up a character definition.

If so, how many are there?

[Zaela]

~7

[picaro11]

Table names?

[picaro11]

~7


table names? How many records can the archive or transactional tables have? I would imagine there is some limit like....1000 records or some time constraint as to which the archive tables will host character records.

[picaro11]

just thinking out loud here.


So I would imagine the archive character records will hold something like what 6 days of data? I could be wrong. A hint I guess would be where the cut-off times are for rezzes, corpse decays, etc. All of these tables are posting transactional data to the archives if at all. And this requires space to host the longer the timers are.


Speaking of which, is there a bath process that executes deletes in the event of a character delete? where all child and parent tables in the character definition are dropped from the database? How big is the undo space? how many records will that hold?

[Grimjaw]

most database tables can hold millions upon millions of rows. so no idea wtf ur talking about.

[Grimjaw]

also not sure what ur talking about when ur combining the topic of archiving character tables with corpse decay. I assume that when you die a new record is created in the corpse table for ur corpse. No impact on the character table except for maybe current zone and experience?

[Grimjaw]

as for archiving the character table. active characters probably never get moved out to a different "archive" table. If that happened, your character would no longer be accessible. And that simply just does not happen here.

as for backing up character data incase of emergency - If anything they take nightly backups or hourly differentials or something.

[picaro11]

Grim.

As for the first comment. My question still stands as long as this memory isn't infinite. Sure there are millions of records, but that space is shared with all other players in the database. And on your 3rd comment, all that back-up space has a limit. And that in essence is what I was trying to get at. At some point that data is no longer retrievable. For sake of performance as well. There has to be a "purge" threshold.

As for the 2nd comment, yeah I can see how that would happen. So the CORPSE table would have a constraint FK_{CHARACTERI_ID}_{#} listed in some ALL_CONSTRAINTS table showing the owning table being the {CHARACTER_ID} table.

Just curious really. I am currently a DEV (just 2 months in after training) for an Oil and Gas software company..just got sick of doin screen validations and thought I would just muse here for a bit.

[Grimjaw]

I'd imagine that they have a backup routine setup like this

7 daily backups that. the oldest is replaced each night
3 weekly backups. the oldest is replaced each weekend
2 monthly backups. the oldest is replaced each month-end

this would allow them to "roll baclk to" any day of the week for the last week, any weekend for the last month, or 2 months prior, if needed.

A lot of databases I worked with don't even have constraints setup (foreign keys). the foreign key is just assumed by the developer and therefore treated as such in their code (aka joining tables using those implied key columns). I forget if the stock EMU like the PEQ database has constraints setup in it. I do not believe so.

[picaro11]

I hear yeah. Well. That certainly brings clarity to my question for sure.

From a functional perspective, I've always understood foreign keys to build structure to databases where it will prevent inconsistency with records in related tables.

So there is 1 death recorded in the 'DEATH' table with a FK constraint where if a character record were dropped from the database lets say, would also require that the record in the DEATH table be dropped as well. That way there isnt a Character definition record remaining in 1 table while there isnt in any other. Joins themselves may not address this issue unless the join were specified in a way to address a cascading deletion in the even of a drop in either of the tables being joined. But i can only assume in this case if it were 2 tables, 2 drop statements would have to be scripted out anyway. This, is regardless of whether there is a foreign key constraint or not. But at least the constraint prevents the deletion of a parent record without including the child.

But of course, I'm sure you already knew that.

In my opinion the lack of foreign key constraints when dealing with related tables would leave a database highly vulnerable to inefficient use of space and a redundancy in code.

I know this is going off topic but its alright i guess. :)

[Extunarian]

As far as I know, there are not tables to track things like death*, or records-over-time. The database represents the state of the world right now. It is not "4 dimensional" if you get what I mean.

Transaction data can be archived in such a way that you can rewind the current state of the database without actually taking a bunch of full copies of the database, as long as they are rolled back in a LIFO manner.

* Tracking a corpse object does not equal tracking a death

EDIT:
For more info about proper schema design w.r.t. foreign keys, etc, read up on database normalization. It's good to have a source of truth in relational databases and have other tables refer to that, rather than copy in such a way as to allow inconsistencies to crop up.

The archiving part, as I described it, is generally referred to as data logging. It does track the DB but the core tables should not really know/care they are being tracked.

[picaro11]

Very nice. Ty Jorg.

Yeah I (believe it or not) would have just assumed rollbacks are on LIFO basis. But there goes my mental "End User" laziness creeping in. xD

Ill drop a line in-game to wave hello sometime! Good to know there is another "see-er of the machine world" who capitulates to the other side every once in a while! haha