Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2007
    Location
    Rotterdam, Netherlands
    Posts
    4

    Unanswered: I would like some help concerning a large and complicated database.

    Hello everyone,
    this is my first post on this fantastic forum.

    As a hobby I’m working on an objective, non-profit website with the inventories of weapon systems (rifles, missiles, vehicles, aircraft, ships ect.) for all the countries of the world. The website is www.dc-database.com. The website currently does NOT use the Access database described below. The new database is to replace the faulty current one. Please note its just a hobby, fighter jets are cool, like sports cars. I do not support violence, war, the weapon industry etc.

    I’ve tried to find the answers to my questions on the internet, but I can’t find the answers I’m looking for. That's why I'm now posting in what seems to be the best DB forum on the net. In short, this is the problem:

    Goal:
    To have a fast database for personal use and being able to get the data in a SQL server for publication on the internet. At first I used the PHPMyAdmin to enter data into the database, but using a browser proved slow. My solution was an Access database and converting in to SQL by dumping it in an .txt file and uploading it in MySQL. Once a good functioning database up and running on the SQL server I should be able to make it accessible on the website on my own.

    Features:
    It should be possible to show a list of weapon systems in use with a certain country (Spain) and for a certain user of a country (US Navy Seals). Each record holds information about the quantity, versions, delivery, country of origin etc. It should also be possible to create a list of countries and/or users of a certain weapon system (F-16 Fighting Falcon).

    Problems:
    {1} The database is getting too large (though its only 8 mb), currently the largest table is 10381 records. Adding more than 70 records to ‘data – Sources’ all the way via ‘list – Types’ will cause Access to crash, run ‘compact and repair database’ and reopen.
    {2} I’ve found out Access has a maximum size of 32 thousand records per table, in the current situation the largest table would eventually have up to 200 thousand entries.
    {3} I know nothing about making forms for entering data. That’s why the tables and relationships may look quite strange; it allows me to enter data easily by clicking the ‘+’ to add data to the subdatasheet. In order to list the weapons by type for every country there’s a ‘que’. This helps me entering data.
    {4} The tables and relations may not be optimal and I believe I've should have used queries over multiple tables.

    Short selection of my many questions:
    [1] Would it be better to use a totally different design of tables? If so, in what way it should be modified?
    [2] I'm using a lookup function, not to show data from another table, but to cut down on typing. ('yu' will give 'Yugoslavia (former)') This isn't a problem right? As it is only one lookup function, not hundreds like in a table.
    [3] By the way I like indexes instead of AutoNumber as it warns me of double entries, but are indexes bad in any way?
    [4] Should I split up the largest table into sections like Aircraft, Vehicles, Ships in order to stay below the 32 thousand record limit?
    [5] Would the solution be to not use Access and to somehow run MySQL locally (for speed) and copy it to the internet SQL server from time to time?

    If I'm starting all over again it would be nice to add some other functions as well. These include having multiple countries of origin for a weapon system and the ability to show for a certain country what weapons were exported to which countries.

    I’m using Access 2003 on my laptop. I have 2007 on my pc, but I’m not familiar with its new interface. I can provide the current database as zipped file (1.4 mb) to those willing to help me out. There is much more to tell, but this post is getting very long already, just let me know what information to give you in case you're so kind to help me out.

    Victor

  2. #2
    Join Date
    Aug 2007
    Location
    Rotterdam, Netherlands
    Posts
    4
    Here's a picture that shows how the database is currently organized:

    http://pixjet.net/img/52d1c59940de15...ationships.JPG

    * 'list - Country' allows me to change a country's name and serves as a lookup.
    * 'list - ID' is used to create categories for presentation, within each category the data is listed alphabetically.
    * 'data - Types' and 'list - Users' This unfortunately creates a double country column.
    * 'data - User' is what its all about. This is the information to be presented on the internet.
    * 'list - Users' and 'list - Types' allow me to change or remove one entry in order to change or remove all those entries further down the line at once. It also acts as a querrie by clicking on the subdatabase.
    * 'data - Sources' this allows me to see what each source (books, internet) say about a weapon system and allows me to store and resolve conflicting data by analizing it.
    * The amentities follow the same layout as the main database, but offer less information and no users. This is used for ammunition, radar systems, etc.
    Last edited by Magister0451; 08-29-07 at 05:58.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Magister0451
    [1] Would it be better to use a totally different design of tables? If so, in what way it should be modified?
    [2] I'm using a lookup function, not to show data from another table, but to cut down on typing. ('yu' will give 'Yugoslavia (former)') This isn't a problem right? As it is only one lookup function, not hundreds like in a table.
    [3] By the way I like indexes instead of AutoNumber as it warns me of double entries, but are indexes bad in any way?
    [4] Should I split up the largest table into sections like Aircraft, Vehicles, Ships in order to stay below the 32 thousand record limit?
    [5] Would the solution be to not use Access and to somehow run MySQL locally (for speed) and copy it to the internet SQL server from time to time?
    1) Oh dear, oh dear, you have gotten your relationships in a twist haven't you!
    I advise you go back to the drawing board on your design, see www.r937.com/relational.html for a very good introduction into database design/normalization.

    2) I don't understand the question

    3) Create autonumbers as primary keys where appropriate. Are indexes bad? Not if they are used correctly.

    4) Aircraft and vehicles (etc) do not share the same attributes (what's the wing span of a tank?). This means that they are separate entities and should be modelled as such.

    5) Is your Access database going to be accessed directly over the net? Sounds a bit odd to me... With your amount of data (200K rows) I think you're going to be happy in the Access environment... However, if you want the data to be accessed directly over t'net then perhaps a move to mySQL or similar may be a good idea.
    George
    Home | Blog

  4. #4
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Access does have some limits but 32,000 rows in a table is not one of them. I work in several databases that have between 50,000 and 800,000 records in a single table. Queries on those tables can be slow sometimes but they are within the limits of access.

    Steve

  5. #5
    Join Date
    Aug 2007
    Location
    Rotterdam, Netherlands
    Posts
    4
    Quote Originally Posted by georgev
    1) Oh dear, oh dear, you have gotten your relationships in a twist haven't you!
    I advise you go back to the drawing board on your design, see www.r937.com/relational.html for a very good introduction into database design/normalization.

    2) I don't understand the question

    3) Create autonumbers as primary keys where appropriate. Are indexes bad? Not if they are used correctly.

    4) Aircraft and vehicles (etc) do not share the same attributes (what's the wing span of a tank?). This means that they are separate entities and should be modelled as such.

    5) Is your Access database going to be accessed directly over the net? Sounds a bit odd to me... With your amount of data (200K rows) I think you're going to be happy in the Access environment... However, if you want the data to be accessed directly over t'net then perhaps a move to mySQL or similar may be a good idea.
    Thank all of you for your replies and PM's. With a little help I've managed to make some great improvements. Good to know there's no 32k limit, oddly many websites do say there is..

    1) Based on the given link I've been redesigning my database without having to start all over again. Its really slim now. I'll post another screen soon. And I'm using queries to find the data I used to look up in subdatasheets.

    2) Thats OK, I've found out its not a problem.

    4) Actually, all weapons do share the same attributes: for instance quantity, date of delivery, country of origin.. The specifications are in another database with tables for helicopters, aircraft, tracked vehicles, wheeled vehicles etc.

    5) I do have an SQL server. Visitors on the website will access the SQL server, even most of the 'plain' text is loaded from the server instead of HTML. The only use of access is entering data, using a browser is slow.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if your site is hosted using a server SQL product then If I were you Id link directly to the server db and not use JET at all. Use Access to link directly to the server

    incidentally I think you are going to have potential problems trying to model a generic standardised database even if you subtype different weapon systems (planes, ships, submarines, tanks whatever), as its difficult to know right now every possible permutation of equipment in any one weapons platform. I think it may be worth while investigating something like the EAV model. But the EAV model may not be appropriate for your current expertese.

    A plane may well be generic, not all planes have engines some have multiple weapons that may be fitted, sometimes mixed, sometimes as options. An example the Nimrod ASW usually has sonar bouys & depth charges/antisubmarine munitions, buit it has also been fitted in the past with close range anti aircraft missiles such as the Sidewinder.. making it possibly the largest plane fitted with close range air combat missiles..

    Most planes have some form of radar set, but some are weather / routing, some are Airbourne Interception, some are ground attack.. each radar has different attributes (eg number of targets that can be tracked concurrently, frequency hopping or not, interfaces to external systems...... the list of deviosuness in military equipment is enormous, there are some seriously scarily clever people in desiging military equipment

    A ship may well be generic, not all military vessels are armed, not all surface ships have the same munitions.. some are optimised for air defence, some for anti sub, some for chasing trawlers.....
    trying to desing a table strucutre that meets that requirement may be complex and will allways be prone to breaking as some bright spark soemwhere adds another capability to an exisitng or new weapons platform, or some sneaky journalist or enthusiast finds out somethign about an existing platform

    HTH
    Last edited by healdem; 08-30-07 at 09:02.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Aug 2007
    Location
    Rotterdam, Netherlands
    Posts
    4
    I'll look into linking directly to the SQL server in the weekend, first I've got some exams to do

    You are right about every system not being generic at all. That's why the generic information will be in pop-up datasheets and all other info is mentioned in the text. Here's an example: http://www.dc-database.com/defence/v...ter&ask_cat=ac (please note its the old db, pop-ups have no data and presentation is faulty due to the direct link) The text and links to the pictures are also extracted from the database.

    The idea is that at first the inventories of all the countries in the world are to be listed, with the quantity, dates of delivery etc. Later on there will be pages for each weapon type, like the link above including specifications like wingspan, weight etc.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •