SitePoint Sponsor

User Tag List

Results 1 to 11 of 11

Thread: database design

  1. #1
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    database design

    Code:
     data in log table
    
    (id) time memberID previous_URL  ...  
    
    (1) 10:01  71         ...         ...
    (2) 10:02  71         ...         ... 
    (3) 10:04   0         ...         ...
    (4) 10:09  251        ...         ...
    (5) 10:10    0        ...         ...
    (6) 10:11  42         ...         ...
    (7) 10:12  0          ...         ...
    (8) 10:13  0          ...         ...
    (9) 10:17  186        ...         ...

    Let's suppose the above data list log records from 10:00 to 10:20.
    4 members visited my site. Their ids are 71, 251, 42 and 186.
    Member 71 records two pageViews while others records one pageView.

    The value '0' in meberID column means 'guest.'

    In average 40 % of the visitors are guests.

    Now I am telling you what I like to ask.
    Which DB design is better or efficient between DB design1 and DB design2 in the following?

    Code:
     DB design1
    
    log table
    
    (id) time memberID previous_URL  ...  
    (1) 10:01 71          ...         ...
    (2) 10:02 71         ...         ... 
    (3) 10:04  0         ...         ...
    (4) 10:09 251         ...         ...
    (5) 10:10   0        ...         ...
    (6) 10:11 42         ...         ...
    (7) 10:12 0           ...         ...
    (8) 10:13 0          ...         ...
    (9) 10:17 186        ...         ...
    
    
    
     DB design2
    
    log table
    
    (id) time previous_URL  ...  
    (1) 10:01   ...         ...
    (2) 10:02   ...         ... 
    (3) 10:04   ...         ...
    (4) 10:09   ...         ...
    (5) 10:10   ...         ...
    (6) 10:11   ...         ...
    (7) 10:12   ...         ...
    (8) 10:13   ...         ...
    (9) 10:17   ...         ...
    
    memberID_log table
    
    (id)  memberID  
    (1)  71         
    (2)  71         
    (4)  251       
    (6)  42         
    (9)  186
    Last edited by dotJoon; Aug 4, 2005 at 03:04.

  2. #2
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I like to add some explanation on this.

    The value '0' in memberID column of DB design1 actually means empty cell.

    The existence of empty cell, I thought, means a kind of waste of cell.

    So I am thinking of DB design 2.

    Make an another table which named memberID_log table and remove memberID column in log table.

    I think removing memberID means more efficient in performance but I am worring about making an another table
    could cause inefficient in performance.

    What do you think about that?

    (The memberID_log table of DB design2 has only two columns: one is (id) which is connected to (id) of log table, and the other is memberID column.)

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    design2 is not so good, leave the memberid in the same log table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thank you for your recommentation for design1.



    Do you think my comment in the following quote makes sense?

    There are 9 cells which are blue-colored for memberID in design1.

    There are 10 cells which are blue-colored for memberID in design2.

    Design2 has one more cell than design1.

    Therefore, design1 is better in the above case.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    it is not so much a question of number of cells, but rather number of tables that need to be joined

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I like to talk/ask about the following case.
    Code:
     design1
    
    log table
    
    (id) time memberID previous_URL  ...  
    (1) 10:01 0          ...         ...
    (2) 10:02 71         ...         ... 
    (3) 10:04 0         ...         ...
    (4) 10:09 0         ...         ...
    (5) 10:10 0        ...         ...
    (6) 10:11 42         ...         ...
    (7) 10:12 0           ...         ...
    (8) 10:13 0          ...         ...
    (9) 10:17 186        ...         ...
    
    
    
     design2
    
    log table
    
    (id) time previous_URL  ...  
    (1) 10:01   ...         ...
    (2) 10:02   ...         ... 
    (3) 10:04   ...         ...
    (4) 10:09   ...         ...
    (5) 10:10   ...         ...
    (6) 10:11   ...         ...
    (7) 10:12   ...         ...
    (8) 10:13   ...         ...
    (9) 10:17   ...         ...
    
    memberID_log table
    
    (id)  memberID  
    
    (2)  71           
    (6)  42         
    (9)  186

    There are 9 cells for memberID in design1.
    There are 6 cells for memberID in design2.
    Therefore, design2 has less cells for memberID than design1.

    Is design1 still better than design2 in the above case because it has less tables that need to be joined?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    sure, of course
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    design1
    
    member table
    
    (id) name profile
    
    (1) Tom     -
    (2) Jane    Jane is ... 
    (3) Kate    -
    (4) John    -
    (5) Chris   -
    (6) Cindy   -
    (7) Jack    -
    (8) Geoge   -
    (9) Rick    Rick is ...
    
    
    design2
    
    member table
    
    (id) name  
    
    (1) Tom     
    (2) Jane    
    (3) Kate    
    (4) John    
    (5) Chris   
    (6) Cindy   
    (7) Jack    
    (8) Geoge   
    (9) Rick    
    
    prfile table
    
    (id) profile
    
    (2)  Jane is ...
    (9)  Rick is ...
    Only Jane and Rick upload her and his profile.
    The rest mebers didn't upload their profile.
    The value of every profile column is very very long paragraph.

    Is design1 still better than design2 in the above case?

  9. #9
    SitePoint Evangelist
    Join Date
    Apr 2005
    Location
    Moscow, Russia
    Posts
    557
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by joon
    Only Jane and Rick upload her and his profile.
    The rest mebers didn't upload their profile.
    The value of every profile column is very very long paragraph.

    Is design1 still better than design2 in the above case?
    If you talking about physical design, the correct answer may also depend on administration requirements, for instance, design 2 is better than design 1 if you want to backup most important member's data like name, password, email, etc. only ('member' table) without profile data resulting reduced time/size of backups. Additional JOIN doesn't matter because it's used when system user accesses member profile

  10. #10
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Tangerine_Dream
    If you talking about physical design, the correct answer may also depend on administration requirements, for instance, design 2 is better than design 1 if you want to backup most important member's data like name, password, email, etc. only ('member' table) without profile data resulting reduced time/size of backups. Additional JOIN doesn't matter because it's used when system user accesses member profile
    Hm~,
    I am afraid I don't understand your sentences clearly...

    First of all, what does "physical design" mean?

  11. #11
    SitePoint Evangelist
    Join Date
    Apr 2005
    Location
    Moscow, Russia
    Posts
    557
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by joon
    Hm~,
    I am afraid I don't understand your sentences clearly...

    First of all, what does "physical design" mean?
    The last stage/activity of the database design process that deals with lowest level of abstraction: tables, columns, data types, constraints (PK, FK, NULL, CHECK, etc), indexes, etc. when a designer considers query performance and database maintenance issues for particular DBMS. Check An Introduction to Database Design: From Logical to Physical


Bookmarks

Posting Permissions

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