SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,921
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Handling Member Attributes

    I am building a website about Small Business. The website is driven by Content and Community. People can surf my website for free and read articles, but to get the real benefits, they register and become a "Member" where they can... 1.) Build a Member Profile, 2.) Post Messages, 3.) Chat with other Members, and so on. (Very similar to what SitePoint does!)

    I am currently building out my Member Profile section. In it, I want to allow Members to share more about themselves and their thoughts to help develop my community.

    So, anyways, I have a "member" table that holds basic Account info like...

    - id
    - username
    - email
    - password
    - first_name

    ...all of which is required and necessary.

    I would like to add some additional fields that could be required or optional. Their purpose to build the "Profile" part. Examples might include...

    (category #1)
    - gender
    - year_born
    - city
    - state
    - country
    - marital_status
    - have_kids
    - former_occupation
    - education
    - photo


    (category #2)
    - business_name
    - business_logo
    - website_url
    - industry
    - date_started
    - business_type
    - no_of_employees
    - what_do_you_sell
    - annual_sales

    Then I would also like to have open-ended questions where people can talk more about their small-business and their views on things, for example...

    (category #3)
    - why did you decide to start your own business?
    - when starting out, what would you tell people to do?
    - when starting out, what would you tell people NOT to do?
    - how do you compete with mega-corporations?
    - what advice would you give someone who is thinking of "taking the leap" and starting their own business?!
    - how do you handle crazy, demanding customers?


    Questions:
    1.) Can I add the Category #1 fields to my Member table?

    I think they all meet 3NF definition with maybe the exception of "photo", but I am only offering one, so that should be okay?!


    2.) What do I do with Category #2 and Category #3 attributes?


    3.) I have read and had some people tell me that *ALL* Member Attributes should go into a dedicated Attribute table. And from what I have read, this is call the "EAV Model" and it sounds scary...

    I can see having my open-ended questions in a many-to-many relationship, but I don't like what I read about EAV.

    Would you put all of my Attributes above into their own table and create a many-to-many relationship?

    If so...
    3a.) What do you do when one field is tiny - "gender" (Boolean) - and another is gigantic - "what advice would you give..." (Text or LongText).

    You would have to store every value as a "Text" type and that sounds crazy?!


    3b.) How do you enforce Data-Type Integrity?

    3c.) How would you get a results set like this...
    Code:
    Name	Gender	Age	Location	Education	Business
    Steve	M	35	Chicago		B.S. Math	Plastics
    Jill	F	41	Dallas		MBA - Finance	Investments
    Ed	M	55	Miami		Dropout		Software
    ...when all of the data is stored in a whole bunch of records?!

    Would you do a Cross-Tab Query??


    And another biggie...

    4.) What is the *proper* way to view Nulls?

    So people say that a "properly-designed" database shouldn't have Nulls in it?!

    Well, what do I do if most of the fields above are optional? (I think that is why some people insist I use that EAV thingy...)

    Is it really a sin if people don't tell you their "Location" or "Year Born"??

    What is the right way to model things to account for this real-world scenario?


    In closing, I *thought* that putting all of the fields above in my "member" table, and breaking out the open-ended questions into a many-to-many relationship was the way to go. But from the people I've talked to, they all seem to imply that I am making horrible decisions...

    Thanks,


    Debbie

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    1.) Can I add the Category #1 fields to my Member table?
    yes


    Quote Originally Posted by DoubleDee View Post
    2.) What do I do with Category #2 and Category #3 attributes?
    the same -- add them to your members table


    Quote Originally Posted by DoubleDee View Post
    3.) I have read and had some people tell me that *ALL* Member Attributes should go into a dedicated Attribute table. And from what I have read, this is call the "EAV Model" and it sounds scary...
    it is, and you shouldn't


    Quote Originally Posted by DoubleDee View Post
    4.) What is the *proper* way to view Nulls?
    use them liberally, for optional data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •