Machine database design

[Tywan Terrell]

Hello Everyone,

I am in the process of creating a data base for a Model Injection machine for a carpet manufacturing company. They are looking to track machine information as well as operator information ie operator mat count, machine down time, operator downtime, mat type etc… The machine makes a mat every 45 seconds so the design requires me to write at least one row vey 45 seconds. There are similar machines that connect to databases that report this data as are started to review the structure of the data model I notice that none of the tables have relationships. The only commonalities I see among the tables are each has a time stamp.  Does anyone know if this is a standard for this type of database design? Any suggestion on how I should design or any insight on best practices for machine database design would be greatly appreciated.

 

Thank you
Tywan

[Brian Erlich]

I have seen setups where the machine ID was included.  I would advise including relationships in your design.  You will want to run reports someday and pk/fk relationships will help.  They may want a BI solution at some point as well.  relationships are very important there also.  Even though the equipment may handle data integrity you still want it managed by the DB.

Brian Erlich

Sent from my mobile phone.   Please excuse any typos.

--
You received this message because you are subscribed to the Google Groups "AtlantaMDF" group.
To post to this group, send email to atlan...@googlegroups.com.
To unsubscribe from this group, send email to atlantamdf+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/atlantamdf?hl=en.

[Dennis Hayes]

In my experience, these types of databases are set up by people who have no clue about databases. I would try to setup a good normalized DB, which would mean migrating old data over, which probably means that is not an option. Who setup the DB, your company, the machine manufacturer, or a third party installer? I would not hesitate to do anything I could / be allowed to do to improve the DB, and would not tend to assume anything was done according to best practices. Thanks, Dennis --- On Tue, 5/15/12, Brian Erlich <brian...@gmail.com> wrote:

[Stuart Ainsworth]

In general, I'd agree with Dennis's observations; however, if the
current db was set up by the machine manufacturer, there may be little
that you can do with the actual database itself. I generally avoid
touching vendor databases directly because you never know what damage
you might do to the application upstream.

Do the tables represesent different machines, or are they lookup
tables or some other attribute? In other words is it:

A: MachineTable1, MachineTable2, MachineTable3, or
B: MachineTable, OperatorTable, MatTable?

If it's A, then it's a logging model, which generally means that
there's not much you can do about it. If it's B, then there may be
some middleware somewhere that's translating the logstream into
normalized structures (just without any data integrity).

It's probably better to avoid changing anything in the database, and
either read the data into another database that you control through
some form of continuous ETL process OR write the reports directly
against the database.

On May 15, 3:17 pm, Dennis Hayes <dennisdot...@yahoo.com> wrote:
> In my experience, these types of databases are set up by people who have no clue about databases. I would try to setup a good normalized DB, which would mean migrating old data over, which probably means that is not an option.
> Who setup the DB, your company, the machine manufacturer, or a third party installer?
> I would not hesitate to do anything I could / be allowed to do to improve the DB, and would not tend to assume anything was done according to best practices.
> Thanks,
> Dennis
>

> --- On Tue, 5/15/12, Brian Erlich <brianerl...@gmail.com> wrote:

>
> From: Brian Erlich <brianerl...@gmail.com>
> Subject: Re: [AtlantaMDF message] Machine database design
> To: atlan...@googlegroups.com
> Date: Tuesday, May 15, 2012, 3:23 PM
>
> I have seen setups where the machine ID was included.  I would advise including relationships in your design.  You will want to run reports someday and pk/fk relationships will help.  They may want a BI solution at some point as well.  relationships are very important there also.  Even though the equipment may handle data integrity you still want it managed by the DB.
> Brian Erlich
> Sent from my mobile phone.   Please excuse any typos.

> On May 15, 2012 2:58 PM, "Tywan Terrell" <tywanterrel...@yahoo.com> wrote:
>
> Hello Everyone,
> I am in the process of creating a data base for a Model Injection machine for a carpet manufacturing company. They are looking to track machine information as well as operator information ie operator mat count, machine down time, operator downtime, mat type etc… The machine makes a mat every 45 seconds so the design requires me to write at least one row vey 45 seconds. There are similar machines that connect to databases that report this data as are started to review the structure of the data model I notice that none of the tables have relationships. The only commonalities I see among the tables are each has a time stamp.  Does anyone know if this is a standard for this type of database design? Any suggestion on how I should design or any insight on best practices for machine database design would be greatly appreciated.
>
>
> Thank you
> Tywan
> --
> You received this message because you are subscribed to the Google Groups "AtlantaMDF" group.
> To post to this group, send email to atlan...@googlegroups.com.
> To unsubscribe from this group, send email to atlantamdf+...@googlegroups.com.

> For more options, visit this group athttp://groups.google.com/group/atlantamdf?hl=en.

[SQLCraftsman]

I worked for many years as a Process Automation Engineer and
understand exactly how this happens. The data recording process is
directly descended from mechanical data-loggers. The job is to log
data and anything that stops that logging is undesirable. Since
referential integrety and unique constraints could block "bad" data
from being recorded, those elements are intentionally excluded. Clean
data is considered an "IT Problem". The machine made the widgets and
recorded the process. Process Automation has done its job, at least
that is how most manufacturing companies will look at it. That IS how
every manufacturing manager will look at it.

As Stuart observed, cleaning up the data after the fact is the best we
can usually expect.

--GNH

On May 16, 8:55 am, Stuart Ainsworth <stuart.ainswo...@gmail.com>
wrote:

[Scott....@hcahealthcare.com]

This sort of process is not just akin to Manufacturing, but to other industries too. If you ask any finance folks what level of detail they want, what is the answer you will get? "Everything"... all the journal entries, all the accounts, etc..... That situation is what leads us to Data Warehouses and Big Data situations.

> > I am in the process of creating a data base for a Model Injection machine for a carpet manufacturing company. They are looking to track machine information as well as operator information ie operator mat count, machine down time, operator downtime, mat type etc. The machine makes a mat every 45 seconds so the design requires me to write at least one row vey 45 seconds. There are similar machines that connect to databases that report this data as are started to review the structure of the data model I notice that none of the tables have relationships. The only commonalities I see among the tables are each has a time stamp.  Does anyone know if this is a standard for this type of database design? Any suggestion on how I should design or any insight on best practices for machine database design would be greatly appreciated.