I don't understand, if you are using Access to store this data surely the speed, accuracy, ease of use is of some concern?
That last one 'ease of use' and your mention of 'modify...painlessly' would probably be helped if you organised the data just a little bit.
For example, how are you going to locate data if it's spread over 10 tables?
Also if you are using Access for storage extracting data might not be to straightforward with this setup.
This is all kind of based on what information you've given so far.
All you've said so far is you have tables with fields in Access and you want to link them somehow, maybe.
Perhaps with further information it'll make more sense.
What sort of information would help?
Perhaps something about what information/data you are working with, how you want to link thngs, eg why is X linked to Z, and Y linked to G
Ok, let me try again. I have to apologize, as I only rarely work with Access, and so I'm not familiar with what information is generally needed to answer these types of questions. You're also correct that I forgot to mention how the data is pulled from the database.
So here is a much more specific explanation. Some of this is probably not directly relevant to the original question, but I'll try to make sure and give too much rather than not enough information this time. This is what I was trying to avoid having to explain with the first few posts, but you asked for it
. Don't worry, I'll get to the point of having the database eventually
.
I develop and maintain a program called TweakMB for the Mount & Blade series of PC games. Much of the code for the Mount & Blade games runs off of values contained in certain text files, which are built from compiled python script. Because these values are stored and read from plain text files, users can easily modify, or "tweak", values that impact things that happen ingame,
if you know where to look in the file. This could be anything from the number of troops you can have in your army, to the amount of gold you earn from completing tasks, to adding completely new functionality to the game.
The purpose of TweakMB is to handle these tweaks automatically for the user. The code in the text files looks something like this " 89 21 1 144115188075856273 22 1 144115188075856786 2204 2 144115188075856787 144115188075856273 ", except there are thousands of lines of it, and each line can contain tens of thousands of values. As you can imagine, your average player doesn't want to mess with trying to figure out which one of these thousands of values represents "renown gained from winning a tournament", or whatever the tweak may be. There's also the risk of messing up your game if you make a bad modification. TweakMB knows exactly where each of these values is located, and can modify the text file automatically based on whatever value the user enters for a given tweak.
So getting to the purpose of the database...TweakMB can perform hundreds of these "tweaks". Each of the tweaks has about 10 "properties" associated with it in TweakMB, such as the name of the file that contains the tweak, the exact field number that contains the tweak value in the line, the name of the control within TweakMB, a detailed description of the tweak (in multiple languages), and so on and so forth.
Currently all of this data is "hardcoded" into TweakMB itself, meaning there are tens of thousands of values scattered all over the place in the code. As you can imagine, this has become a pain to deal with. My goal for the next major version of TweakMB is have the bulk of the tweak properties stored in a structured fashion in a database, at which point the program can pull the data from the database and handle it however it needs to. If it matters, TweakMB is written in VB.NET using Visual Studio 2008.
So your first question is probably why don't I just set up one big table with the records representing each unique tweak, and the columns representing each property of that tweak. The answer is that TweakMB also supports these tweaks for numerous user mods as well as the original game. The Mount & Blade games are designed to be very modder-friendly, and there are many great mods available for it. However, the properties for each tweak are potentially different with each mod, and must be stored separately for each version of each mod.
Finally, the database is setup as follows:
Each table represents one of the properties of the tweaks (ie, filename, description, default value, etc)
Each record within the tables represents a unique tweak, and each one of these tweaks has to be represented in every table
Each column within the tables represents a specific user mod
The reason that the tables are setup by tweak property instead of being setup by user mod is that when I'm updating the values, it helps to be able to see at a glance which values were previously used for other mods or game versions.
When it's set up this way, I can create a simple query to pull in the records for any given individual mod, which is all that needs to happen as far as TweakMB is concerned. For example, if I need to pull the tweak properties for all 500 tweaks for the mod "Native 1.143", all I have to do is pull the "Native 1.143" column from each table. The first column in each table is the "Tweak ID" column, which is what identifies each specific tweak and is also what ties the tables together in the query.
The reason I say that speed is not important is that TweakMB accesses the database a total of one time each time it runs. It connects to the database, determines which pre-built query should be used for the mod the user selected, and then pulls the entire query into a DataTable object in memory. The database itself is never touched again after that one time. No part of it is ever modified by TweakMB. Even with the probably non-ideal way I have it setup now, this part of the process still happens pretty much instantaneously, which is why I'm not terribly worried about speed.
So after all that, back to the original dilemma. This is what I want to accomplish with this database:
Store the appropriate data --> done and ready to go
Easily consolidate the appropriate data with a query --> done and ready to go
Ability to retrieve queried data using VB.NET application --> done and ready to go
Ability to easily add new tweaks to the database when needed --> not done
Currently if I want to add a new tweak, I have to manually go into each of the 10 tables and add the same Tweak ID to each table. This annoys me. What I'm looking for is a way to have all of the Tweak IDs stored in one central location, and then have the other 10 tables setup in a such a way that they automatically pull the Tweak ID values from this one central location. This way I only ever have to add a new Tweak ID one time, and I can be assured it's added in the same way on each table. I would still have to manually modify the actual tweak property values specific to each table, but there's no getting around that.
Anyway, if you read all that and you're still paying attention, I salute you. If you fell asleep a long time ago, I don't blame you
.
