Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2010
    Posts
    12

    Unanswered: Urgent Help Design/normalize table ?

    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!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sysenm View Post
    My problem is how should i split the table?
    why do you feel that it needs splitting???
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2010
    Posts
    12

    RE: Why split

    Thanks for your reply!

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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sysenm View Post
    Data of one department should only be visible to that department.
    create views, not separate tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2010
    Posts
    12

    starles become

    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).

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 ... ;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2010
    Posts
    12
    I will go with views. Thank you very much for your quick reply.

  8. #8
    Join Date
    Nov 2010
    Posts
    2
    the table is already normalised

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •