Hi,

I need to design a database, but i i just don know if i am doing it
correctly.

Basically, i have equipment which is sending me a lot of gauges
readings (high limit, low limit, current level), electrical readings,
hydraulic readings, alarms levels such as Fuel level, temperature
level, etc etc, about 200 different types of readings, continuously. I
may have different types of equipments and although most of the data
are the same, except some data types and its availability may be
different., depending on the type of equipment.

I want to keep this readings in a database, and this database will be
updated constantly. I will update display using the readings from the
DB. Eventually i also want to use the data from the database to plot
chart or run simulation.

Should i create all of this different gauges readings in one big
single table? If i do so, i only have one long row of data then for a
particular equipment.If i put in different table, i can't see how i
can create relationship among my tables with such kind of data.

Can you on the possible ways of putting this equipment real time
readings into DB?

On Mar 26, 10:43am, panab...@gmail.com wrote:
> Hi,
>
> I need to design a database, but i i just don know if i am doing it
> correctly.
>
> Basically, i have equipment which is sending me a lot of gauges
> readings (high limit, low limit, current level), electrical readings,
> hydraulic readings, alarms levels such as Fuel level, temperature
> level, etc etc, about 200 different types of readings, continuously. I
> may have different types of equipments and although most of the data
> are the same, except some data types and its availability may be
> different., depending on the type of equipment.
>
> I want to keep this readings in a database, and this database will be
> updated constantly. I will update display using the readings from the
> DB. Eventually i also want to use the data from the database to plot
> chart or run simulation.
>
> Should i create all of this different gauges readings in one big
> single table? If i do so, i only have one long row of data then for a
> particular equipment.If i put in different table, i can't see how i
> can create relationship among my tables with such kind of data.
>
> Can you on the possible ways of putting this equipment real time
> readings into DB?

You don't necessarily have to have a relational database, it is just
useful for data integrity. You could have one table per piece of
equipment if that makes sense. If you started repeating data, that
would be a prompt to pull it out and make a related table.

On Mar 26, 11:37pm, RogBa...@gmail.com wrote:
> On Mar 26, 10:43am, panab...@gmail.com wrote:
>
>
>
>
>
> > Hi,
>
> > I need to design a database, but i i just don know if i am doing it
> > correctly.
>
> > Basically, i have equipment which is sending me a lot of gauges
> > readings (high limit, low limit, current level), electrical readings,
> > hydraulic readings, alarms levels such as Fuel level, temperature
> > level, etc etc, about 200 different types of readings, continuously. I
> > may have different types of equipments and although most of the data
> > are the same, except some data types and its availability may be
> > different., depending on the type of equipment.
>
> > I want to keep this readings in a database, and this database will be
> > updated constantly. I will update display using the readings from the
> > DB. Eventually i also want to use the data from the database to plot
> > chart or run simulation.
>
> > Should i create all of this different gauges readings in one big
> > single table? If i do so, i only have one long row of data then for a
> > particular equipment.If i put in different table, i can't see how i
> > can create relationship among my tables with such kind of data.
>
> > Can you on the possible ways of putting this equipment real time
> > readings into DB?
>
> You don't necessarily have to have a relational database, it is just
> useful for data integrity. You could have one table per piece of
> equipment if that makes sense. If you started repeating data, that
> would be a prompt to pull it out and make a related table.- Hide quoted text -
>
> - Show quoted text -


In this case, i most probably have only just one row of data, with
possibly 200 columns to cater for all the readings i get from my
equipment, in one single table. Does this sound ok..?

if i need to plot data later, should i use fix time stamp interval as
the row in my table?

On Mar 26, 11:56am, panab...@gmail.com wrote:
> On Mar 26, 11:37pm, RogBa...@gmail.com wrote:
>
>
>
>
>
> > On Mar 26, 10:43am, panab...@gmail.com wrote:
>
> > > Hi,
>
> > > I need to design a database, but i i just don know if i am doing it
> > > correctly.
>
> > > Basically, i have equipment which is sending me a lot of gauges
> > > readings (high limit, low limit, current level), electrical readings,
> > > hydraulic readings, alarms levels such as Fuel level, temperature
> > > level, etc etc, about 200 different types of readings, continuously. I
> > > may have different types of equipments and although most of the data
> > > are the same, except some data types and its availability may be
> > > different., depending on the type of equipment.
>
> > > I want to keep this readings in a database, and this database will be
> > > updated constantly. I will update display using the readings from the
> > > DB. Eventually i also want to use the data from the database to plot
> > > chart or run simulation.
>
> > > Should i create all of this different gauges readings in one big
> > > single table? If i do so, i only have one long row of data then for a
> > > particular equipment.If i put in different table, i can't see how i
> > > can create relationship among my tables with such kind of data.
>
> > > Can you on the possible ways of putting this equipment real time
> > > readings into DB?
>
> > You don't necessarily have to have a relational database, it is just
> > useful for data integrity. You could have one table per piece of
> > equipment if that makes sense. If you started repeating data, that
> > would be a prompt to pull it out and make a related table.- Hide quoted text -
>
> > - Show quoted text -
>
> In this case, i most probably have only just one row of data, with
> possibly 200 columns to cater for all the readings i get from my
> equipment, in one single table. Does this sound ok..?
>
> if i need to plot data later, should i use fix time stamp interval as
> the row in my table?- Hide quoted text -
>
> - Show quoted text -

200 columns is fine. I am not sure what you mean about the time stamp
as a row. It is common to use a column to hold a timestamp value.

On Mar 26, 1:54pm, RogBa...@gmail.com wrote:
> On Mar 26, 11:56am, panab...@gmail.com wrote:
>
>
>
>
>
> > On Mar 26, 11:37pm, RogBa...@gmail.com wrote:
>
> > > On Mar 26, 10:43am, panab...@gmail.com wrote:
>
> > > > Hi,
>
> > > > I need to design a database, but i i just don know if i am doing it
> > > > correctly.
>
> > > > Basically, i have equipment which is sending me a lot of gauges
> > > > readings (high limit, low limit, current level), electrical readings,
> > > > hydraulic readings, alarms levels such as Fuel level, temperature
> > > > level, etc etc, about 200 different types of readings, continuously.I
> > > > may have different types of equipments and although most of the data
> > > > are the same, except some data types and its availability may be
> > > > different., depending on the type of equipment.
>
> > > > I want to keep this readings in a database, and this database will be
> > > > updated constantly. I will update display using the readings from the
> > > > DB. Eventually i also want to use the data from the database to plot
> > > > chart or run simulation.
>
> > > > Should i create all of this different gauges readings in one big
> > > > single table? If i do so, i only have one long row of data then for a
> > > > particular equipment.If i put in different table, i can't see how i
> > > > can create relationship among my tables with such kind of data.
>
> > > > Can you on the possible ways of putting this equipment real time
> > > > readings into DB?
>
> > > You don't necessarily have to have a relational database, it is just
> > > useful for data integrity. You could have one table per piece of
> > > equipment if that makes sense. If you started repeating data, that
> > > would be a prompt to pull it out and make a related table.- Hide quoted text -
>
> > > - Show quoted text -
>
> > In this case, i most probably have only just one row of data, with
> > possibly 200 columns to cater for all the readings i get from my
> > equipment, in one single table. Does this sound ok..?
>
> > if i need to plot data later, should i use fix time stamp interval as
> > the row in my table?- Hide quoted text -
>
> > - Show quoted text -
>
> 200 columns is fine. I am not sure what you mean about the time stamp
> as a row. It is common to use a column to hold a timestamp value.- Hide quoted text -
>
> - Show quoted text -

Pan...

First thing you need to know is how are your readings done. Is it done
every x amount of time? Do you have to prompt for each readings? Do
you get a table (excel) emailed to you every x amount of time.

You mention Constantly, but that is not a good word to use when
recording data. You might record the data every second, milisecond,
nanosecond, and still not constant. However, that is good news.

I would suggest you have two tables. One for machines and the other
for readings.

Your [Machines] Table has all the information for each of your
machines plus a MachineID to identify each machine (This should be
however your source information identifies each machine). Your
[Readings] Table will have those 200 readings plus a few more, should
be like this:

[Readings]
===========
[ReadingID] (autonumber) key
[MachineID]
[TimeStamp] (date/time)
[electrical]
[hydraulic]
[Fuellvl]
[Temperaturelvl]
...
[etc]

Now each row has the readings for one machine at a particular time. So
then if you filter on Machine 1 then you will see all your readings
across time for machine 1.

Also, if you do not have any information on the machines only the
MachineID then there is no need to have the [Machines] Table.

- GL

(panabong@gmail.com) writes:
> Basically, i have equipment which is sending me a lot of gauges
> readings (high limit, low limit, current level), electrical readings,
> hydraulic readings, alarms levels such as Fuel level, temperature
> level, etc etc, about 200 different types of readings, continuously. I
> may have different types of equipments and although most of the data
> are the same, except some data types and its availability may be
> different., depending on the type of equipment.
>
> I want to keep this readings in a database, and this database will be
> updated constantly. I will update display using the readings from the
> DB. Eventually i also want to use the data from the database to plot
> chart or run simulation.
>
> Should i create all of this different gauges readings in one big
> single table? If i do so, i only have one long row of data then for a
> particular equipment.If i put in different table, i can't see how i
> can create relationship among my tables with such kind of data.
>
> Can you on the possible ways of putting this equipment real time
> readings into DB?

One alternative is have one row for each reading. That makes it easier
to deal with if the values can come in different frequencies, as
each row also could have a timestamp go with it. I assume that you keep
some history of th readings. Then again, if the values from different
domains, this can be messy.

But I really know too little of your requirements and your system to
be able to say anything authorative.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Mar 27, 3:48am, Guillermo_Lopez <g.lo...@iesdr.com> wrote:
> On Mar 26, 1:54pm, RogBa...@gmail.com wrote:
>
>
>
>
>
> > On Mar 26, 11:56am, panab...@gmail.com wrote:
>
> > > On Mar 26, 11:37pm, RogBa...@gmail.com wrote:
>
> > > > On Mar 26, 10:43am, panab...@gmail.com wrote:
>
> > > > > Hi,
>
> > > > > I need to design a database, but i i just don know if i am doing it
> > > > > correctly.
>
> > > > > Basically, i have equipment which is sending me a lot of gauges
> > > > > readings (high limit, low limit, current level), electrical readings,
> > > > > hydraulic readings, alarms levels such as Fuel level, temperature
> > > > > level, etc etc, about 200 different types of readings, continuously. I
> > > > > may have different types of equipments and although most of the data
> > > > > are the same, except some data types and its availability may be
> > > > > different., depending on the type of equipment.
>
> > > > > I want to keep this readings in a database, and this database willbe
> > > > > updated constantly. I will update display using the readings from the
> > > > > DB. Eventually i also want to use the data from the database to plot
> > > > > chart or run simulation.
>
> > > > > Should i create all of this different gauges readings in one big
> > > > > single table? If i do so, i only have one long row of data then for a
> > > > > particular equipment.If i put in different table, i can't see how i
> > > > > can create relationship among my tables with such kind of data.
>
> > > > > Can you on the possible ways of putting this equipment real time
> > > > > readings into DB?
>
> > > > You don't necessarily have to have a relational database, it is just
> > > > useful for data integrity. You could have one table per piece of
> > > > equipment if that makes sense. If you started repeating data, that
> > > > would be a prompt to pull it out and make a related table.- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > In this case, i most probably have only just one row of data, with
> > > possibly 200 columns to cater for all the readings i get from my
> > > equipment, in one single table. Does this sound ok..?
>
> > > if i need to plot data later, should i use fix time stamp interval as
> > > the row in my table?- Hide quoted text -
>
> > > - Show quoted text -
>
> > 200 columns is fine. I am not sure what you mean about the time stamp
> > as a row. It is common to use a column to hold a timestamp value.- Hide quoted text -
>
> > - Show quoted text -
>
> Pan...
>
> First thing you need to know is how are your readings done. Is it done
> every x amount of time? Do you have to prompt for each readings? Do
> you get a table (excel) emailed to you every x amount of time.
>
> You mention Constantly, but that is not a good word to use when
> recording data. You might record the data every second, milisecond,
> nanosecond, and still not constant. However, that is good news.
>
> I would suggest you have two tables. One for machines and the other
> for readings.
>
> Your [Machines] Table has all the information for each of your
> machines plus a MachineID to identify each machine (This should be
> however your source information identifies each machine). Your
> [Readings] Table will have those 200 readings plus a few more, should
> be like this:
>
> [Readings]
> ===========
> [ReadingID] (autonumber) key
> [MachineID]
> [TimeStamp] (date/time)
> [electrical]
> [hydraulic]
> [Fuellvl]
> [Temperaturelvl]
> ...
> [etc]
>
> Now each row has the readings for one machine at a particular time. So
> then if you filter on Machine 1 then you will see all your readings
> across time for machine 1.
>
> Also, if you do not have any information on the machines only the
> MachineID then there is no need to have the [Machines] Table.
>
> - GL- Hide quoted text -
>
> - Show quoted text -

ok, for hydraulics or electrical, in fact there are just name which
consist of a set of readings as well. For instance, for hydraulics, it
actually consists of two hydraulics pump, and each pump has Pressure
reading and Flow reading.

In this case, should i just insert a column each for
Pressure_Hydraulics1, Flow_Hydraulics1, Pressure_Hydraulics2,
Flow_Hudraulics2 into my main table (that contains abt 200 readings
including timestamp, etc etc as you propsed?)

Or will it be better if i take out the readings for hydraulics and
create one separate table for both Hydraulics1 and Hydraulics2 such
as:

Table Hydraulics
==============
Readings ID (auto key)
Timestamp
Pressure
Flow
Hydraulics ID (either 1 or 2)

In this case, i will have 3 tables, one main table consist of about
150 readings, one table for hydraulics like above and one also for
electricals like the hydraulics table above. But there is no
relationship whatsoever to the main table. Is this ok?

On Mar 27, 9:47am, panab...@gmail.com wrote:
> On Mar 27, 3:48am, Guillermo_Lopez <g.lo...@iesdr.com> wrote:
>
>
>
>
>
> > On Mar 26, 1:54pm, RogBa...@gmail.com wrote:
>
> > > On Mar 26, 11:56am, panab...@gmail.com wrote:
>
> > > > On Mar 26, 11:37pm, RogBa...@gmail.com wrote:
>
> > > > > On Mar 26, 10:43am, panab...@gmail.com wrote:
>
> > > > > > Hi,
>
> > > > > > I need to design a database, but i i just don know if i am doingit
> > > > > > correctly.
>
> > > > > > Basically, i have equipment which is sending me a lot of gauges
> > > > > > readings (high limit, low limit, current level), electrical readings,
> > > > > > hydraulic readings, alarms levels such as Fuel level, temperature
> > > > > > level, etc etc, about 200 different types of readings, continuously. I
> > > > > > may have different types of equipments and although most of the data
> > > > > > are the same, except some data types and its availability may be
> > > > > > different., depending on the type of equipment.
>
> > > > > > I want to keep this readings in a database, and this database will be
> > > > > > updated constantly. I will update display using the readings from the
> > > > > > DB. Eventually i also want to use the data from the database to plot
> > > > > > chart or run simulation.
>
> > > > > > Should i create all of this different gauges readings in one big
> > > > > > single table? If i do so, i only have one long row of data then for a
> > > > > > particular equipment.If i put in different table, i can't see how i
> > > > > > can create relationship among my tables with such kind of data.
>
> > > > > > Can you on the possible ways of putting this equipment realtime
> > > > > > readings into DB?
>
> > > > > You don't necessarily have to have a relational database, it is just
> > > > > useful for data integrity. You could have one table per piece of
> > > > > equipment if that makes sense. If you started repeating data, that
> > > > > would be a prompt to pull it out and make a related table.- Hide quoted text -
>
> > > > > - Show quoted text -
>
> > > > In this case, i most probably have only just one row of data, with
> > > > possibly 200 columns to cater for all the readings i get from my
> > > > equipment, in one single table. Does this sound ok..?
>
> > > > if i need to plot data later, should i use fix time stamp interval as
> > > > the row in my table?- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > 200 columns is fine. I am not sure what you mean about the time stamp
> > > as a row. It is common to use a column to hold a timestamp value.- Hide quoted text -
>
> > > - Show quoted text -
>
> > Pan...
>
> > First thing you need to know is how are your readings done. Is it done
> > every x amount of time? Do you have to prompt for each readings? Do
> > you get a table (excel) emailed to you every x amount of time.
>
> > You mention Constantly, but that is not a good word to use when
> > recording data. You might record the data every second, milisecond,
> > nanosecond, and still not constant. However, that is good news.
>
> > I would suggest you have two tables. One for machines and the other
> > for readings.
>
> > Your [Machines] Table has all the information for each of your
> > machines plus a MachineID to identify each machine (This should be
> > however your source information identifies each machine). Your
> > [Readings] Table will have those 200 readings plus a few more, should
> > be like this:
>
> > [Readings]
> > ===========
> > [ReadingID] (autonumber) key
> > [MachineID]
> > [TimeStamp] (date/time)
> > [electrical]
> > [hydraulic]
> > [Fuellvl]
> > [Temperaturelvl]
> > ...
> > [etc]
>
> > Now each row has the readings for one machine at a particular time. So
> > then if you filter on Machine 1 then you will see all your readings
> > across time for machine 1.
>
> > Also, if you do not have any information on the machines only the
> > MachineID then there is no need to have the [Machines] Table.
>
> > - GL- Hide quoted text -
>
> > - Show quoted text -
>
> ok, for hydraulics or electrical, in fact there are just name which
> consist of a set of readings as well. For instance, for hydraulics, it
> actually consists of two hydraulics pump, and each pump has Pressure
> reading and Flow reading.
>
> In this case, should i just insert a column each for
> Pressure_Hydraulics1, Flow_Hydraulics1, Pressure_Hydraulics2,
> Flow_Hudraulics2 into my main table (that contains abt 200 readings
> including timestamp, etc etc as you propsed?)
>
> Or will it be better if i take out the readings for hydraulics and
> create one separate table for both Hydraulics1 and Hydraulics2 such
> as:
>
> Table Hydraulics
> ==============
> Readings ID (auto key)
> Timestamp
> Pressure
> Flow
> Hydraulics ID (either 1 or 2)
>
> In this case, i will have 3 tables, one main table consist of about
> 150 readings, one table for hydraulics like above and one also for
> electricals like the hydraulics table above. But there is no
> relationship whatsoever to the main table. Is this ok?- Hide quoted text -
>
> - Show quoted text -

It depends really on the entities of your system. The way i see it
your hidroulics has 2 pumps, and will always have two pumps and your
calculations might never combine the pumps. If this is the case then
you can add it to the main table as columns. Same for electrical.

However if your system has X amount of pumps and X amount of electical
devices. and this amount is large and also can change the number
frequently. Then i suggest you have a different table for each entity,
just like you mentioned. There is really no need to have a relation
but if you want one, make the relation as the Readings or as the
TimeStamp. So you have an autonumber and a ReadingID that relates to
the main table.

Another reason you might want to have use seperate tables is if you
are calculating stuff withing the entities. If i want to measure the
Average of both pumps, with the first options i need a cruel formula,
but its very easy when it has its own table.

If you are analysing the readings independently then you don't really
need a relation because they are not related.

- GL