Designing a Dynamic Database

[igglebop]

Not sure if "dynamic database" is the right name for this, but here's the challenge:

I'm designing a database that will be installed at a variety of facilities. The goal is for each facility to have an easy way to store their daily logs in a single location. The challenge is that each facility collects different data on a daily basis.

The more efficient way to do it is to design a table for each facility that looks like this example:

tblDailyLog

• Date (PK)
• OperatorName
• Meter1Reading
• Meter2Reading
• FlowRate

But of course, not every facility will have those data points. So here's what I was thinking instead:

tblReadingTypes

• ReadingTypeID (PK)
• ReadingTypeName

tblReadings

• ReadingDate (PK)
• OperatorName

tblReadingLog

• ReadingDate (FK) (CPK)
• ReadingTypeID (FK) (CPK)
• Result

I would then have "Meter 1 Reading", "Meter 2 Reading", and "Flow Rate" as elements in the ReadingTypes table.

It seems awfully redundant to repeat the ReadingDate foreign key over and over with each reading of each day, but I think it's the best way to give it the flexibility to work at other facilities.

Any thoughts or comments are appreciated!

[pbaldy]

Your first "efficient" layout does not follow the rules of normalization, and I would not go that way even if you only had one location. Your second method does, and that's the way I'd go. It's obviously better for multiple locations, and is even better for one. What would happen if a month from now they got a third meter to read? With your first design, you're making design changes to the table, forms, queries, reports, etc. With your second design, you don't make any design changes. A normalized design is also dynamic.

[igglebop]

Ah, I hadn't realized that it broke the rules of normalization. So you're saying that, even though there's only one set or readings per date (thus not repeating data), it doesn't meet the 3rd normal form because the Readings group of attributes doesn't inherently relate to the Date attribute. Is this correct?

I'm fairly new to databases, but it's important to me to learn good design from the get-go.

Ryan

[pbaldy]

I'm not strong with the actual rules, but I suspect it doesn't meet 1st normal form. What you have I would call repeating groups. I would call each a "reading", and the flaw might be more obvious if you named the fields Reading1, Reading2, Reading3. Here's one link on normalization, and I would interpret your fields as being covered in the discussion on first normal form, specifically figure 7:

http://www.r937.com/Relational.html

[igglebop]

Thanks for the help!

[pbaldy]

No problem, and welcome to the site!