Extended System Data

[Hemdian]

Originally posted by Claire Rand:
Anyone else think about doing a decent capacity relational database..

Yup, its called Traveller Universe, and its sold (internationally) under the BITS label from Gnometrader. At present it uses Borlands InterBase as its RDBMS engine but there is a port to MS SQL Server/MSDE in the pipeline. Either way its in 3NF and gives you the option of using sector/subsector/hex codes as a system address or ring/ray as a system address. It even has an early development LDD if you're into such things.

The import/export functions in the Windows GUI client support a number of formats and is customisable. And while it uses UWPs at its base, it can translate these into GURPS format at runtime.

And it makes pretty maps (including J6 maps).

Regards PLST

 

[Hemdian]

Originally posted by robject:
Well, as opposed to going round in circles, maybe I'm "zeroing in" on a target.

By the way, I've posted several of the concoctions floating on this thread here:

http://home.comcast.net/~downport/uwp/

Slight correction: you list the "Fenris Format" as "a tentative beta format for Universe", but I think you mean "a tentative beta format for HE2".

Regards PLST

 

[robject]

I'm sorry. Universe is anything but tentative.

 

[FlightCommanderSolitude]

Regarding databases, what are your thoughts on defining a table to store UWP data? I can see three options:

</font>

• 1. Store the entire profile as a string</font>
• 2. Store each profile "digit" as a char</font>
• 3. Store each profile digit as whatever type is most natural for it</font>

Option 1 seems the simplest to implement and the least useful. Any operation would require extra code to pull information out of the profile. Score: poor.

Option 2 has the advantage that you can access discrete attributes directly in your query language, and so is more flexible. However, you'll likely need to do some conversion between the stored characters and their numeric values. Eg, if I store 'A' for hydrographic score, I need to be able to convert that to a value of 10 somewhere; consider a query for systems with "hydrographics greater than 8". Score: Good, with caveats.

Option 3 has the advantage that your data representation is most proper. Eg, starport type would be stored as a character, whereas hydrographic score would be stored as a number. However, as a consequence, attributes need to get translated into a hex code in order to print out a UWP. That might be better than option 2 however. Score: Good, with caveats.

Perhaps it would be feasible, with option 3, to have another table that just encoded numbers with their hex equivalents (dropping letters i and o for clarity) and then do joins when required.

Any ideas or comments?

 

[RainOfSteel]

Originally posted by FlightCommanderSolitude:
Regarding databases, what are your thoughts on defining a table to store UWP data? I can see three options:

</font>

• 1. Store the entire profile as a string</font>
• 2. Store each profile "digit" as a char</font>
• 3. Store each profile digit as whatever type is most natural for it</font>

You are discussing two different things as the same type. One, Entity design, and two, Domain designation for the Attributes of an Entity. They are entirely differnet worlds.

The database type we have been discussing these last few posts is "relational". Storing the UWP as a single Entity (i.e. a single line (of whatever Domains)) is quite simply out of the question. It would violate 1NF, and cause a mountain of other problems. Oh, we'll display it in the old UWP-line format, come printing time, but it will not be stored that way.

A Galaxy contains many Entities, and those Entities and the facts about them will wind up, during the Physical Design process, as many different tables.

Galaxies, Sectors, Subsectors, Hexes (Hex-Rays and Hex-Rings), Stars, Bodies, Polities, etc.

The UWP-line itself will be but one view (and a limited one at that) of extant Survey Information.

 

[tjoneslo]

Originally posted by FlightCommanderSolitude:
Regarding databases, what are your thoughts on defining a table to store UWP data? I can see three options:

</font>

• 1. Store the entire profile as a string</font>
• 2. Store each profile "digit" as a char</font>
• 3. Store each profile digit as whatever type is most natural for it</font>

Any ideas or comments?

The proper way to do this is either 2 or 3, and use a lookup table to contain both the other version of the data and the expanded text.

I store them as characters ('A', '2', etc.) then the lookup table contains a table name, code, value and description. For example: hydrographic, 'A', 10, '100% water, water world';

The choice between using 2 and 3 depends largely on how many queries are going to be using the code and how many are going to be using a value. Either way the query is fairly simple.

 

[FlightCommanderSolitude]

tjoneslo: Yeah, I pretty much answered my own question: it seems that option 3 is the best. I was just interested in other people's implementation experiences. Storing the name of a table in another table sounds like a horrible and unneccesary hack, although I've encountered a few situations where it was expedient.

 

[clair_rand]

actually i'd fire off the 'system' as the record key, simply given some sort of long int key number, the co-ordinates are a property of the system this way (plus the Db can work with integer keys this way). 2d/3d. Go 3d, if you want 2d its easy, use zero the third dimension at creation time (i'm assuming the ability to generate systems is part of all this.

UWP values? hmm. I'd be tempted not to store them at all (except maybe as quick refs in calcuated fields stored to save time) but store the actual data.

Thus world size is stored as diamter in Km, atmosphere stored as a pressure/composition/temerature/whatever else seperate structures. With a routine to spit out the UWP from this.

Thus since its doing the maths, you can havea UWP and you can have any level of detail you want over and above this. Yes the UWPs get a table of their very own for the text string lookups but the actual UWP is a derived value *not* the core data.

actually all you store in the 'core' table is the system id, orbital raduis from the parent object and the objects referecne key.

Note the parent can be a star, or a psuedo object for the centre of gravity in a binary system etc. Each object only knows how far it is from its centre of orbit. A scan for a 'object' can provide details of its direct children, grandchildren are found by walking the data tree.

This also allows for several other data tables, each using a core table object ref as a key. One table for stars, one for gas giants and one for more solid planets (and maybe one for strange stuff).

Its even possible to allocate a deep space station via this method.

Actual 'object' co-ordinates personally i'd store as a tripple integer (or tripple double maybe) all child objects don't need this infomation, thus it is stored in yet another key table.

Thus we have the following tables

[core]
id, x, y, z

[object]
id, core.id, parent, radius, name

[planet]
object.id, uwpdata, notes

[star]
object.id, stellar type, notes

[gas giant]
object.id, gg-data, notes

[unusual objects]
object.id, notes

thus the looks ups *can* be a bit nasty, but since the code only needs generating once this is not a problem.

the object table is used mostly to handle stuff common to everything in the system, the other tables are used to save space, since so much of the data will not be needed for the most common object (a red dwarf). this method also allows for additional tables to tie into the objects table, maybe a 'GM comments' table to store game specific stuff (with a key for the game) etc.

this also avoids storing table names, you essentially scan all the child tables for a given 'object.id' to see if it holds any infomation about the object in question. sorting by the orbital radius puts things in order. naturally if one of the children of a given object has an orbital raduis of zero and is in the star table, its reasonable to assume tis the primary.

 

[tjoneslo]

Originally posted by FlightCommanderSolitude:
Storing the name of a table in another table sounds like a horrible and unneccesary hack, although I've encountered a few situations where it was expedient.

I'm not actually using a table name as the first itme, it's just a key to seperate the values from all the other values in the lookup table.

The keyed lookup table is an old hack. In theory you would have a lookup table for each set of value you need to lookup. So you'd have a hydrographic_lookup table, an atmosphere_lookup table, etc.

But it means there are a huge number of mostly identical small tables, which consumes extra memory and causes the database to go beserk on doing lookups.

So by creating a single lookup table and adding a "key" column to it, you can pin the table in memory and lookups are lightning fast.

 

[RainOfSteel]

Originally posted by tjoneslo:
So by creating a single lookup table and adding a "key" column to it, you can pin the table in memory and lookups are lightning fast.

That type of design is known as a "One True Lookup Table."

See: dbazine.com: One True Lookup Table

 

[TheEngineer]

Hi !

Just as a side note:
Right now I store the complete TU data (about 27000 systems) in just one textfile.
Hierachical elements are stored via a level identifier (0=system e.g. star; 1=subobject e.g. planet; 2=subsubobject e.g. moon; 3=subsubsubobject e.g. station) and the orbital distance from parent body.
So the TU data file also contains generated system detail data.

The textfile without system details data is just 2400 kB. Each detailed system included adds around 1-2 kb of data.
Even with ALL TU systems equipped with detail data the filesize is "just" around 30 MB.
What I consider to be somehow funny is, that its no problem for any modern PC, to load that tiny chunk of data just into memory (I started computing with a ZX81, so it still appears weird to me).

Thats one reason why I skipped the DB hosted approach to store TU data last year.
For large scale data manipulation I regulary use text file db drivers or a spreadsheet program (well, the 64000 row limit here makes me worry, but I get along with only a few hundred detailed systems...).
If I just need "read only" access to the data, I usually read the chunk just into memory, with some indexes build up during load.

But anyway, proceed please

 

[FlightCommanderSolitude]

Originally posted by tjoneslo:
I'm not actually using a table name as the first item, it's just a key to seperate the values from all the other values in the lookup table.

Gotcha, so a table with a record of, say, 'ATMO', 'C' and 'Insidious', but also a record 'PORT', 'E' and 'Frontier installation; no facilities'. This model works well, especially when you don't have to worry about the data changing very often - and in this case, we don't.

Originally posted by Claire Rand:
UWP values? hmm. I'd be tempted not to store them at all (except maybe as quick refs in calcuated fields stored to save time) but store the actual data.

Thus world size is stored as diamter in Km, atmosphere stored as a pressure/composition/temerature/whatever else seperate structures. With a routine to spit out the UWP from this.

This sounds dead on arrival. It might work for size and population, but how would you store tech level, law level or government codes? It does have the advantage that "real" data is stored, but for my purposes at least that's not interesting.

Anyways, thanks for the input. So, robject, what's the state of the art in extended system data formats?

 

[robject]

Whoa, I've been cued? Hang on, let me make something up.

I used to think that SOA for eXtended system data (SOAX?) was going to be a variant of that used by HE2.

Marc Miller, however, has recently mentioned using the spreadsheet as the instrument of torture for canonical system data -- in other words, the Standard UWP data, plus some extra stuff, will be stored in rows. So the "Official" data for the Imperium will be in a master spreadsheet, and views of it can be constructed from it, in the form of traditional SEC files, descriptive text, et al.

Your Input Is Important

In fact, here's the kind of data the Official UWP Spreadsheet row will contain. It looks like there are duplicate entries here, which may indicate that he's deciding what to keep and what to not use -- or to have different arrangements for utility's sake. What do you think?

</font><blockquote>code:</font><hr /><pre style="font-size:x-small; font-family: monospace;">Sector name
Hex number
System (mainworld?) name
UWP
Trade Codes
Remarks
Economic Extension digits
Cultural Extension digits
Nobility codes
PBG
Worlds in the system (perhaps)
Allegiance
Primary star data (incl close companion)
Companion star data
Far primary star data (incl close companion)
Far companion star data
UWP Broken into fields:
Starport code (1 = A...)
Size number
Atmo number
Hydro number
Pop number
Gov number
Law number
TL number
P number
B number
G number
Starport letter code
One cell for each possible trade code & remark code
(Ag As Ba De Fl Hi Ic In Lo Na Ni Po Ri Va Tp Tn Wa Pag Pin Pri An Cp Ci Di Fo Qu Ux)
One cell for each nobility level that may be present
(B C D E F G)
Sector name + hex num in one cell
Scout survey ID ( = last survey date?)
M1000 name
M0 name
RoM name
ZS name
Economic extension cells:
Res
Lab
Inf
Bar
Cultural extension cells:
Core
Group
Ethics
Symbols</pre>[/QUOTE]Now a traditional spreadsheet can hold, what, 64,000 entries? Probably OK for a sectorful of SOAX. A dense sector could push the limit, but a traditional sector would fit fine.

So, I suspect that SOAX might could be a greatly expanded Child Of HE2 in a spreadsheet.

Also, I suspect that SOAX would always be uncanonical -- which is probably a Good Thing. Think of it as a modernized kind of GENIE archive.

 

[FlightCommanderSolitude]

I have a bunch of problems with this as a "general" format, although if particular bits of data were released in this format (eg, concurrent with the publication of some supplement detailing a some sector) that would be fine - that would at least save us the step of typing it all in.

Some problems:

a. data duplication
b. what's the deal with "numbers" in the UWP data
c. the field for "worlds in the system" clearly needs rethinking
d. trade and remark codes each have their own column, but a player might want to add their own codes, which would mean altering the structure of the database (in this case by adding a column)
e. some of the data is overly setting-specific

I lost you on SOA - WTF?

 

[robject]

It's not really a general format, any more than the UWP line was a general format. Your point about the trade and remark columns is a good one; however, considering that this is for "Official" data I think it's not unreasonable. Ditto that for the Milieu names.

Another difficulty comes in when one wants to regress UWP data for milieux.

I suspect that the numeric breakdowns, as well as the indivudual cells for codes, is for the benefit of spreadsheet macros.

I don't see a problem with duplicate data, assuming the data is consistent and correct. But perhaps that's a big assumption?

I personally think that "worlds in the system" ought probably to go away.

 

[tjoneslo]

I'm not sure when the last time you used a spreadsheet program, but these mondern, update to date ones can handle any number of entries, with multiple 16,000 pages of 16,000 rows each. I'm quite sure you can fit all of the SOAX data into one sheet for all of Known Space.

How much programming can we assume the end user needs/wants to do? The UWP in a single string and the UWP in seperate columns is redunant, but depending upon the level of programming knowledge assumed, I would pick one or the other.

Why are assuming full sector sizes for this? My experience in running and playing traveller games is a subsector or two is more than sufficent space, without overwhelming the players/referee with too much data.

Instead of having the M1000 name, M0 name, ROM name, ZS Name, you should plan on having an entire set of data for each mileu. UWP will change over that time, as will allegiance, trade codes, bases, Economic and social extensions, etc.

Bases? Are there no longer any notation for military bases in the system?

Worlds in the system should be combinded into the PGB code: PWGB code? In fact, take the population multipler out of the PGB code and replace it with the world count.

Don't use the "one cell for each possible code". Spreadsheets have Substr() and Instr() functions for a reason.

 

[Jeffr0]

Originally posted by just robject:
In fact, here's the kind of data the Official UWP Spreadsheet row will contain. It looks like there are duplicate entries here, which may indicate that he's deciding what to keep and what to not use -- or to have different arrangements for utility's sake. What do you think?

Personally, I don't think in terms of spreadsheets... I think in terms of database tables.

If I had to choose one of the other, I'd prefer to have all codes broken up into seperate columns rather than lumped together into a single string. If you have it in columns, it's easy to derive the resulting string. Heck, you could even have dozens of formats depending on what you were doing. Going in the other direction is trickier, though.

If everything is broken up into columns, then writing queries will be easier, among other things. What would Dr Codd say if you didn't?!

 

[LairdDunAfton]

Question:

Is there a product that will read the SAR format from the Galactic Core Site?

 

[robject]

Laird: what's that? Give me a link and I'll take a peek.

Jeff: I think in columns, too. It's annoyingly long-winded and inefficient to put one value in each column, but as you say, it's also very flexible.

In fact, if I had my druthers, I'd put the actual value in the cell, rather than the code: use 8250 (km) for diameter instead of 5 (5000 miles). But I digress.

 

[tjoneslo]

Originally posted by Laird Dun'Afton:
Question:

Is there a product that will read the SAR format from the Galactic Core Site?

The SAR (Sector archive) files are simple PKZIP files, which you can open with any ZIP program (WinZip works perfectly). The same is true of the GAR (Galaxy Archive) files.

The files inside are all in text format, which you can read with any text editor. If you have Galactic, the help files describe the text files, and others can be figured out.

I have an (unreleased) java program which will read the SAR files into a map. I know Heaven & Earth 2 will read them. And, of course Galactic itself will read them.