Urgent Help Design/normalize table ?

sysenm · #1 (**permalink**) 10-28-10, 06:27

Hello All!

I've recently started working with a MySQL database project and I need your guidelines for designing/splitting database. The schema of the main table is as follows:

------------------------------------------------------------
ID | Zone | Area | Indicator | Value | Timestamp |
------------------------------------------------------------

The database is related to the environmental/chemical indicators planted at different locations inside a factory. The factory is divide into zones and areas. Each zone contains multiple areas. Currently the table has a flat structure similar to above but now I have to divide/split among two departments with each department having some complete zones and some specific areas like:

Department 1
-------------
Zone 1: area 1, 2, and 3
Zone 2: area 1 and 3
Zone 3: area 1, 2,

Department 2
------------

Zone 1: area 4
Zone 2: area 2
Zone 3: area 4, 5
Zone 5: area 1, 2, 3, and 4

My problem is how should i split the table? Should i use views? The table is populated every 20 minutes with around 40-50 thousand rows and all the data is flushed only after a week. Please help me with your valueable suggestions.
Thank you very much!

r937 · #2 (**permalink**) 10-28-10, 06:37

Quote:

Originally Posted by sysenm

My problem is how should i split the table?

why do you feel that it needs splitting???

sysenm · #3 (**permalink**) 10-28-10, 07:09

Thanks for your reply!

Both the departments want privacy. Data of one department should only be visible to that department.

r937 · #4 (**permalink**) 10-28-10, 07:19

Quote:

Originally Posted by sysenm

Data of one department should only be visible to that department.

create views, not separate tables

sysenm · #5 (**permalink**) 10-28-10, 07:25

Thank you for your reply!

Will there be any performance penalty due to views because the total number of zones is 9 and the total number of areas is 30 ? Also the fact that the number of records is huge (around 100-150K per hour).

r937 · #6 (**permalink**) 10-28-10, 07:34

no, the views will have no performance problem

a view is, after all, only a stored query definition

Code:

CREATE VIEW dept1
AS
SELECT ... FROM ... WHERE dept = 1;

GRANT SELECT ON dept1 TO dept1userA, dept1userB, dept1userC ... ;

CREATE VIEW dept2
AS
SELECT ... FROM ... WHERE dept = 2;

GRANT SELECT ON dept2 TO dept2userX, dept2userY, dept2userZ ... ;

sysenm · #7 (**permalink**) 10-28-10, 07:56

I will go with views. Thank you very much for your quick reply.

ypulse · #8 (**permalink**) 11-01-10, 04:18

the table is already normalised

Internet Services

Web Development

Digital Marketing

Consumer Tech