How many DB tables make up a single Character Definition?

picaro11 · #1 08-26-2014, 10:17 AM

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 · #2 08-26-2014, 10:30 AM

picaro11 · #3 08-26-2014, 10:33 AM

Table names?

picaro11 · #4 08-26-2014, 10:35 AM

Quote:

Originally Posted by Zaela [You must be logged in to view images. Log in or Register.]

~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 · #5 08-26-2014, 11:05 AM

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 · #6 08-26-2014, 11:15 AM

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

Grimjaw · #7 08-26-2014, 11:18 AM

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 · #8 08-26-2014, 11:20 AM

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 · #9 08-26-2014, 11:33 AM

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 · #10 08-26-2014, 02:40 PM

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.