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
- 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…
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