I initially started this to help me keep track of all the accents I had to miss out in the first stage of my mapmaking last year. I quickly found I needed to add in other information, such as the region, country, and type of settlement. Then I had to add in non-settlement labels, such as those for hills, forests, etc., because they too included accents.
Now I find myself with a rather large list, which it strikes me could surely quite easily be plugged into a database to form a rather nice basis for a much larger project.
Unfortunately, I know almost nothing about databases.
But I know that at least a few people in our community have a solid understanding of these things, so I would like to request their (your?) help.
What I want to do initially is make a solid database design, into which we can import the information I currently have in Excel spreadsheet format. After that, I should be able to input any further additions straight into the database, as well as being able to easily look up and get reports on regions when it comes to updating my maps.
In the long term, a lot more information could be added, resulting in a strong database of Mystaran geography. I mentioned that this was a side-project, but actually this will eventually be quite relevant to my Secret Project(TM). ;)
So... Can anyone help me?
Region (Known World, Alphatian Sea, Gulf of Hule, Yazak Steppes, etc.)
Country (Karameikos, Hule, etc.; Independent for areas unclaimed by a country)
Province (Estate of Marilenev, Barony of Ulthuinn, etc.)
Name (of the settlement, terrain, feature, etc. in question)
Type (City, Lake, Hills, Forest, etc.)
Population (only relevant for settlements of course)
Notes (Capital, Imperial Capital, Regional Capital, etc.)
Sources (GAZ1, D171, etc.)
Eventually I could add in a lot more, including Description, Country 2 (for things like forests that extend over two or more countries), Province 2, Leader, Notable Sites, Famous Residents, etc. Currently these things are not practical because I have it in a spreadsheet.
Offhand, a drilldown set of fields to identify location (continent, campaign area, nation, province), population field, racial composition, could all be useful.
Don't know the fields I have offhand, but I'll check when I get home from work.
[EDIT] From looking at your second post, Thorf, I can see that we have a lot of similar fields for our projects (region, names, notes, provinces, etc.). I wonder if we might be able to cross-reference both lists at some point, when/if we get our respective db's completed.
I certainly would love to offer my services to you, Thorf, but as I explained earlier in another post, my time is all eaten up with r/l and other projects of my own.
I am in a similar position to Gawain - I'm happy to offer advice and answer questions but can't help too much with actual work I'm afraid.
In its simplest form a database could be pretty much like your spreadsheet, but then it would not be any more useful. To get more "power" (for example to have a feature exist in more than one country as in the forest you mentioned) you could set up a database in Access or MySQL. You can probably do something with Excel but that would kind of be like knocking a nail in with a screwdriver. MySQL is probably the best choice since you can get webspace providers who host MySQL databases so if you plan on using the database as part of a website in the future it would be ready. And its free.
Each of the fields you listed would become a table of values. Then you would have a single table in the "middle" which represents the key "unit" of data you are interested in - a "feature" in your case. To have a feature link to 2 countries say, you would then need an intemediate table between the "feature" table and the "country" table.
My website uses a similar setup. For example I have a table called adventures (with fields like adventure_id, adventure_name, adventure_text) and a table called characters (character_id, name, description etc.). Then there is an adventure_characters table which is just (adventure_id, character_id) to say "Adventure X involved Character Y" and would have one entry for each character on each adventure.
This is way too big a topic to fit in a forum post, but if you google "beginner mySQL tutorial" or "beginner database tutorial" you can probably learn enough to muddle through.
I would sort out exactly what you want the database to do first though as it will help you design it. Basically the more flexible the design the more powerful queries you will be able to run, at the cost of making the data more difficult to enter and maintain.
Any questions let me know and I'll do what I can to help.
Not much in the way of help here either but I would suggest that you look into Open Office's Database. Comes in v2.0+
http://www.openoffice.org/
I use the OO Writer for all of my DnD stuff. The whole office suite is similiar to M$ Office and cost $0.00 to use.
--Ray.
One thing I would consider doing before you put it into an actual database is to decide ahead of time what data fields you want in there, especially if teh database is to be more that just a flat table. In my experience, once you start making the db do anything clever, adding extra fields becomes an extremely tiresome exercise.
Offhand, a drilldown set of fields to identify location (continent, campaign area, nation, province), population field, racial composition, could all be useful.
I've been hearing this from various other places too - design the database before you get started.
I have a list of the various fields I'll probably want, and how they all need to work. What I really need advice with is how to set up the database.
For example, what is the difference between having a numerical ID key and a text ID key in a table? I could for example make a table of countries, including their region, continent, world and plane. In that case, would it be best to make the country the key for that table (thus forcing it to be unique), or is it better to assign a numerical key ID, which of course has no relevant meaning?
Also, I am a little confused as to how to set up the tables. The country table above seems like a good design. Let's say I have another table for settlements, including things like population, alternate names, type of settlement, description, leader, etc. I can presumably easily cross-reference the country entry from my countries table. How about provinces? Is there an easy way to fit it into this? It seems as if it would be good in between the country and settlements tables, so do I put it in a third table of its own?
Next we come to NPCs. I might want to have a table of NPCs, to tie in with the settlements and countries tables, and add in NPCs for each area. For now I would be happy with just a name and perhaps a very short description. Does this mean another table? How should I link it into the others, considering that I may want to specify a settlement, or a province, or perhaps just a country for them.
From looking at your second post, Thorf, I can see that we have a lot of similar fields for our projects (region, names, notes, provinces, etc.). I wonder if we might be able to cross-reference both lists at some point, when/if we get our respective db's completed.
Sounds interesting. It would be excellent if we could contribute somehow to each other's work. By the way, I have been using Access so far too.
If a few people are interested in contributing to different areas, we could eventually build this into a much larger project, and create a full Mystara database. That could be really useful for fuelling other projects. Imagine if you wanted to get a list of NPCs in a certain country, and you could just go and ask the database. Find the ones you want, and get a readout of all their stats, in whatever rules set(s) you need.
I don't know quite how these things work, but there certainly seem to be those of us here who do. If we could get some help to get things set up, and design a good, solid database, it would then be a "simple" matter of putting in all the data.
I am in a similar position to Gawain - I'm happy to offer advice and answer questions but can't help too much with actual work I'm afraid.
Just some advice will hopefully go a long way. :D Thank you for replying.
In its simplest form a database could be pretty much like your spreadsheet, but then it would not be any more useful. To get more "power" (for example to have a feature exist in more than one country as in the forest you mentioned) you could set up a database in Access or MySQL. You can probably do something with Excel but that would kind of be like knocking a nail in with a screwdriver. MySQL is probably the best choice since you can get webspace providers who host MySQL databases so if you plan on using the database as part of a website in the future it would be ready. And its free.
Interesting. I had a look at MySQL, but couldn't get it set up on my computer properly. I think I would need some help to get it working, although if we were to put the database on a server somewhere that would probably be a different matter.
Quick question: is there any way to transfer a database from Access to MySQL? In other words, would I be able to convert whatever I have done so far, if I converted to MySQL?
Each of the fields you listed would become a table of values. Then you would have a single table in the "middle" which represents the key "unit" of data you are interested in - a "feature" in your case. To have a feature link to 2 countries say, you would then need an intemediate table between the "feature" table and the "country" table.
This is the part I usually get lost at. :embarrass
My website uses a similar setup. For example I have a table called adventures (with fields like adventure_id, adventure_name, adventure_text) and a table called characters (character_id, name, description etc.). Then there is an adventure_characters table which is just (adventure_id, character_id) to say "Adventure X involved Character Y" and would have one entry for each character on each adventure.
So in simple terms, lots of tables is best? And it sounds like you use numerical IDs for each table.
This is way too big a topic to fit in a forum post, but if you google "beginner mySQL tutorial" or "beginner database tutorial" you can probably learn enough to muddle through.
Yep, I've been reading through help files, but ultimately they can't tell me exactly what I want to know without a lot of trial and error of my own, I think.
I would sort out exactly what you want the database to do first though as it will help you design it. Basically the more flexible the design the more powerful queries you will be able to run, at the cost of making the data more difficult to enter and maintain.
Ah, so we come to it. Well, basically I want to be able to make a database of Mystaran geography, and later get it to output gazetteers for each area/country, as well as various lists using a range of fields.
Of course I could do all that manually, but it would end up becoming a Word document combined with lots of spreadsheets, with the information duplicated again and again.
I think I can help with this, but working through design specifics on a message board may be a bit limiting. Any thoughts on a way to share the raw data so I can make a guess at how to throw structure at it?
Lets say you have you country table :
1 - Country_1 - Leader_1 - Language_1
2 - Country_2 - Leader_2 - Language_2
You have a city table :
1 - City_1 - Population_1
2 - City_2 - Population_2
3 - City_3 - Population_3
4 - City_4 - Population_4
And a table where you record the country the city belongs to.
(Country) - (City) with numerical ID keys :
1 - 1 (County 1 has city 1)
1 - 2 (County 1 has city 2)
1 - 3 (County 1 has city 3)
2 - 4 (County 2 has city 4)
(Country) - (City) with text ID keys :
Country_1 - City_1 (County 1 has city 1)
Country_1 - City_2 (County 1 has city 2)
Country_1 - City_3 (County 1 has city 3)
Country_2 - City_4 (County 2 has city 4)
The text ID key is easier to read, but the information are redondant (writed twice in the base) Let's assume you made a mistake in spelling the name of Country_1 and it should be Pays_1 . With text ID keys you'll have to change all occurrences of Country and you can forget some, which means your base is not anymore correct. With the numerical ID key, you just have to change the name of the country once.
Hope you got my point of view.
If you have any other questions, please ask me, i'll try to help you as much as i can.
Having a numerical ID key instead of a text ID key makes joins between tables easier.
This is very true, and I'll also add that using text fields introduces the problem of typos, since fields have to match exactly. Even an extra space messes things up.
Setting up a database like this requires just a bit of upfront planning. Pick out some "objects" that are going to serve to describe your data. In this case, we've probably got countries, cities, maybe people, maybe resources (like in the Darokin gazetteer), and maybe some kind of "ethnicity", as that plays a big role in Mystara. You'll have tables that describe all these, and I think you'll be okay building them. Then you'll have what are known in the database design industry as "resolver tables" (they are also sometimes called "fact tables" in the data warehousing industry) that link these tables together. Shughuy described them pretty well.
The key is getting tables that make sense. If you don't do that, you might as well just use Excel (by the way, using Excel to store data is something so evil that only Bargle would do it ... Excel is great for some things, but it's not meant to be a database). As I said before, my recommendation is to make sure that each row of your tables has a unique, numerical ID, otherwise typos become not merely unprofessional, but a danger to your data.
Giving specific advice is a little tough in this situation, because it sounds like Secrecy (TM) is more important that getting help, which I can understand. But without more details of your goals (aka "requirements"), it's really difficult to offer direction.
In terms of database engine, I guess you could use MySQL or Access (using Access is becoming an increasingly bad idea for web publishing, however). MySQL is nice in that it's cheap, and it works nicely with PHP. The downside to those technologies is that there are a TON of very bad amateur programmers out there and the "cheap-as-free" cost tends to attract them. I, personally, do all my little personal side projects with SQL Server. You can get decent web hosting that includes SQL Server for about $25 a month. I doubt that the database project you're describing would require more than that.
If you've got any questions, PM me. Database design and web development is what I do for a living.
My website uses a similar setup. For example I have a table called adventures (with fields like adventure_id, adventure_name, adventure_text) and a table called characters (character_id, name, description etc.). Then there is an adventure_characters table which is just (adventure_id, character_id) to say "Adventure X involved Character Y" and would have one entry for each character on each adventure.
It would figure that 2 minutes after saying that there are a ton of bad MySQL/PHP websites out there, i would see the one waferthinninja did, which is a fantastic, well-done site. If you're going to go with MySQL, he (and his site) would probably be a good reference for you.