Hello. I have a series of products on various customer sites that need data logging. They already have a primative file based form of data logging which works, I can retreive the data, I can also view live data through applications I have developed for mobile phones and pc. The problem is that it is not easy to use the data for cross analysis between sites or product types. I therefore come to the conclusion i need a database which will be magical, I however have no experience other than using dBaseIII at college some years ago. The selection of the database I see as being a whole different thing, it may be relevant to the systems I use to collect the data it will most likely be free. I have spent some time browsing these forums and reading varous sources on how to design the database. Unfortunately for me, it seems 99% of what I have read would indicate databases are for people with customers who buy a product who are then required to pay an invoice with a certain amount of tax, I can't really see a clear correlation to my problem although there most likely is.

So my 'problem(s)':

1:
The on site products have differing amounts of data that need to be recorded (according to product type), also the products can have attached another series of product (sub products) that can also have differing amounts of data (according to product type).
Products can change site and locations of that site, sub products can change between products and locations within that product.

2:
I would like to have a local database for the site which certain data can be accessed by the customer and certain data to be accessed by a service engineer.

3:
I would also like to have a master database that pulls data from each site that can only be accessed by my colleages or myself.

I have come up with the following concept to address point 1.

I create the following tables in a new database:

One for Site information:
- {Name, IP Address, Port, etc}

One for each product type named as product number:

- {Index (primary key), DateStamp, Site, Site_ID, Serial Number, Firmware ID, data array (columns)}

- Datestamp is moment in time of data record.
- Site is where the product is located in the world.
- Site_id is where the product is located within that site.
- Firmware_ID tells what software is running on it.
- Data is a series of bytes, integers and bools from sensors, hardware outputs and software outputs.

One for each product sub type named as product number:

- {Index (primary key), DateStamp, Product_Serial, Product_ID, Firmware_ID, data array (columns)}

- Datestamp is moment in time of data record.
- Product_serial is the product it was attached to at that moment in time.
- Product_id is the location on the product it was fitted to at that moment in time.
- Firmware_ID tells what software is running on it.
- Data is a series of bytes, integers and bools from sensors, hardware outputs and software outputs.

I can see I should be able to cobble some SQL together to manage point 2 but how do i go about acheiving point 3?! Would I need to have the site information on the customer end database as it would be irrelevant?

Does this seem like a reasonable solution to be able to extract data and keep data efficiency, more importantly will it work?! I'm not bothered about processing performance, I want to keep disk space and data traffic low.

Examples of useful data searches would be:

- Comparison data (inputs or/and outputs) for a product type(s) on site(s) for a given time window.
- Performance data for a product type regardless of site.
- Performance data for a product regardless of site.
- Retrive a full picture of a data for a product and its connected sub products for a given time window.

Thanks for reading.

John.