Chris, You don't mention the database you intend to use for this application (e.g. SQL Server 2000, SQL Server 2005, MS Access, Oracle, DB/2, etc.), so it is kind of hard to advise you on the impacts of the two options you are considering.
However, here are the basic trade-offs: One Table Design: - If you use a single table, you won't need to have a process for creating a new table whenever you have a new sensor added to the mix (or, for instance, when you go to an installation with 100 (or 250) sensors instead of the average of about 50 that you had planned for); - If you set up your indexes correctly, you can speed up the access to the data and it won't be such a load when you select data to graph; - Having the data all in one table can simplify your selection of multiple sensors for comparative graphs; - Having the data all in one table also simplifies the coding of the application or the stored procedures to do things like insert the data or to perform any other processing of the data. Multiple Table Design: - As you pointed out, the data for any given sensor would be segregated from all other sensors; - You could spread the tables across multiple databases and just link the databases. Personally, if _I_ were doing this, the first thing I would do is probably get SQL Server 2005 as a minimum starting database (you can down load SQL Server 2005 Express for free to develop against). I would carefully craft 1 (and only 1) table for the raw data and also carefully set up indexes (especially covering indexes) to allow the reporting and graphing functions to work well. I would also crate stored procedures to handle the data manipulations (all inserts, any updates or deletions, and any other functions that can possibly be done by the stored procs). I would avoid MS Access like the proverbial plague! It doesn't scale well and it doesn't handle large enough tables/databases. It also is a tad slow in responding to multiple activities at the same time (e.g. you bunches and bunches of sensors ;-). With SQL Server 2005, you can perform some data partitioning even within one table. Ralph D. Wilson II Database Developer 9038 Woodland Trace Boerne, TX 78006 Home: (830) 981-8884 Cell: (210) 387-7744 Fax: (830) 981-5726 Any sufficiently advanced technology is indistinguishable from magic. A.C. Clark -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Chris Stebbing Sent: Wednesday, June 11, 2008 1:29 AM To: Delphi DB List Subject: Database Design Question Hi All, I have a question on database design if I may. The application that I will be working on is an industrial one where we will be logging data from a number of different sensors all at differing rates. The "current" data value is used to initiate actions and is always held in memory, but past data is most commonly graphed regularly. By far the largest amount of data is generated by logging these sensors, and the most common use for this data is simply graphing. An installation could range anywhere from 5 sensors to 100 sensors. The main question I have is whether it would be better to have all these sensors log into one table, or have them log into a separate table per sensor. The amount of data can build up quite an amount over time, and filtering one large table (or querying one large table) takes an amount of time. Having the data already separated by sensor would be a huge head start. Thanks for any comments and my apologies if this question is out of line here. Regards, Chris. _______________________________________________ Delphi-DB mailing list Delphi-DB@elists.org http://lists.elists.org/cgi-bin/mailman/listinfo/delphi-db
