How do you model User Attributes?

I am building a website for a local group I belong to. It will allow registered Users to create profiles, view other Users' profiles, and talk in an online forum.

In a User's Profile, I am thinking of the following fields...

- Username
- First Name
- Photo
- City
- State
- Occupation
- Interests
- Etc.
- Etc.
- Etc.

I could just put all of that in my "member" table, but I am wondering if a many-to-many wouldn't be better for both performance and scalability?! (IMG:style_emoticons/default/iconfused.gif)

Something like...


member -||--------|<- member_details ->0---------||- attributes


Benefits:

1.) As "attributes" change, I don't have to mess with my "member" table

2.) It would be more efficient storage since not everyone will fill out every "attribute" - most of which are optional

3.) It would make the "member" table more efficient. (I have heard that if you have tons of fields in a table, that even querying one or two fields will be slower because of all of the extra fields?!)


What is the best way to tackle this?

BTW, in my User Profiles, I may also have open ended questions that could get fairly large like...

- How did you get involved in Boy Scouts?

- What was your favorite adventure as a child?

- How do you feel things have changed since your were a boy?

Sincerely,


Rob

One way to think of this is to ask yourself whether any given attribute can be variable for any given member.

Some attributes are clearly not variable: firstname, lastname, dateofbirth.

Other attributes are clearly variable, either concurrently or sequentially: phonenumber, address, occupation.

You need to decide whether capturing variability is important to your database. If you want to know that a member has three different addresses, then you'll need to account for that in a separate table. If you only care about the one address to which you send mail, then you don't need to separate out addresses.

The same questions apply to any attribute you consider. YOU decide what the rules are for your database and then you build a data model to support those rules.

The myth about performance in wide tables is just that, a myth. UNLESS you include every single field in a query. That would indeed be inefficient.

"SELECT * FROM tblAnyTable" is an unfortunate choice, regardless of the number of fields in total.

"SELECT Field1 FROM tblAnyTable" is as efficient as it can be (given proper indexing, which is a whole 'nother consideration)

If you intend to capture extended text answers, you'll want a separate "Notes" table, consisting of a foreign key to members, a foreign key identifying the questions, and a Memo field in which the extended answers can be stored.


This post has been edited by GroverParkGeorge: Mar 31 2012, 12:17 PM

I just want to capture attributes to help a User describe him/herself and help people build relationships and create a community.

I am not building histories on people, so if you have moved 5 times in the past 5 years, I do not care, other than you are free to mention that under "About Me".

So, what I hear you saying is to put all of the User Info fields/attributes in the User table itself? And to NOT do the many-to-many I described in my OP. Right?




Why is that?

That seems inconsistent with your advice above...

For longer textual responses, each question would be unique and thus it is NOT like a blog.

I might have questions that are displayed in a User's profile like...


BTW, since this is for the web, I would be using MySQL, so I'm not sure if your answer about breaking things out was assuming I'd be using MS Access?

Sincerely,


Rob

"I just want to capture attributes to help a User describe him/herself and help people build relationships and create a community."

That's a goal, not a business rule. While you do need goals for a project, you also need to define the business rules that apply to your database.

No, I'm not saying you should put all of the attribute fields in the member table, nor am I saying you should create one-to-many tables, nor am I saying that you should create many-to-many tables. I'm saying that you decide what information you want to store, how you want to store it, and then you design tables to support those rules. That's the role of business rules in designing a database.

Variability in attributes is one important consideration in deciding which table design is most appropriate to your database although it's not the only one.

For example, you've articulated the business rule for addresses. "One is enough." With that rule, you don't need a many-to-many relationship. You don't even need a separate address table, in fact. You can keep the address fields in the member table. That means, when a member moves, you update that address, losing any history for it. That's the business rule you have chosen, so that's an appropriate table design. If you had a different business rule, you'd need a different design.

Many-to-many relationships are a separate matter still. They are needed when you have a situation where one or more items in TableA can be linked or related to one or more items in TableB either simultaneously or sequentially AND you need to track all of those multiple relationships..

For example. You seem to be thinking of a Boy Scout organization. Okay, there are a limited number of ranks a Scout can attain, Tenderfoot, First Class, etc. That is a closed domain of values. You can enumerate every possible rank in a Lookup table, you can be 100% certain that you've accounted for all ranks, and you can be reasonably certain that there will not be additional ranks introduced in the near future. That's often referred to as a "Closed Domain" of values. Perfect for Lookup tables.

However, you can use that Rank Lookup table either as a one-to-many lookup for members, or as a many-to-many lookup. And to repeat, which you choose depends on the business rule you choose.

If you want to know ONLY the currently held rank, you can establish a one-to-many relationship between the Lookup Table and the member table and keep that field upated as the members advance through the ranks.

If you want to know the HISTORY of ranks held, then you can create a many-to-many relationship by adding a junction table between members and ranks.

Merit badges are still a different question. I am quite sure that a scout can simultaneously hold many different merit badges. That's a many-to-many relationship.

Once again, for each attribute of importance to you, you'll ask the same questions and decide whether you have to capture data for a one-to-many or a many-to-many relationship for that attribute. There's no "one size fits" all rule here. I can't stress this enough. You make the business rules and then you design tables to support them.

====
Yes, for extended text fields, a separate table with memo fields is better, but not for the same reasons we design other related tables. Sorry, I should have explained why you want to do that.
Corruption is a possibility in any Access database. Memo fields tend to be more often associated with corruption. It's a practical thing, therefore, to separate memo fields out into a separate table so that if one of them does get corrupted, it's less likely to cause problems in the parent table.

However, you plan to use MySQL, so that is possibly less of a consideration. You might find that storing long text strings in the same table is less risky in MySQL. Still, for performance, I'd probably have separate table anyway, and only store records with long text strings if the member actually entered them.

From a relational design view, though, it's not that critical.

This post has been edited by GroverParkGeorge: Mar 31 2012, 08:40 PM

I hear what you are saying, but that's not helping a lot, because I was sorta hoping to have someone say, "This is how I would design it" *LOL*

Here is some information I would like in a Member's Profile...



I think all of those belong in the "Member" table because they are unique, non-repeating values that relate directly to the primary key (i.e. the person) and I don't need history. (It is just a profile which is a "snapshop" of the here and now.)

Either beneath the above info, or on another tab which is still within the Member's Profile, I would like to offer fields where Members can share their thoughts and views on things, and reveal more about themselves if they so choose.

So some candidate questions include...



I would hope that most people would answer at least some of these questions. Of course each response will be unique. I will not need to keep a history again. So how do I handle these?

You are saying break them out into a separate table?

What is the relationship then? Isn't it a one-to-one relationship?

I was just going to use something like a varchar(1024) because I think that is enough for a person's profile.

Oh, and another question...

Whether I put the responses to those questions in my "member" table or another table, how would I store them?

What fields would I have?

And what would I call each field? (It's not as simple as "Fullname", "Age", "Location" and so on!)

Would I have to come up with a shortened version of the question?

Or just do something generic like "response1", "response2", "response3"?

Sincerely,


Rob

P.S. if I come up with an initial design and I outgrow it, is it a disaster to just redesign the backend later? I mean, nobody gets things perfect the first time, right?

As you've stated, you want a snapshot, so that's how you should design your member table. Make it a snapshot.

Given the fact that any individual can respond to one or more of your questions, and may not answer any of them, that is a good candidate for a child table. It will be one-to-many with members because a person can answer 0 questions, 1 question or many questions. I would also NOT fall into the trap of the repeating group, "response1", etc.

This table should have three key fields and a "response" field.
One of the keys is the Primary Key for the table.
You also need a Foreign Key to the member table to associate the member with their response(s).
You also need a Foreign Key to a table listing questions, so you know which question each response is for.
And your response field can be varchar(1024) if you think that is enough room for people to answer your questions.

That, of course, means you DO have a lookup table for the questions posed. It'll have a primary key and a text field for the question.

You should NOT have a field called "FullName" unless you don't care to do any filtering or sorting on names later (there's that business rule question again, what do YOU want to do?) Most databases have individual fields for "FirstName", "LastName", etc.

"Age" is a problem because people have birthdays every year. No matter what people put into an age field, it will be outdated and incorrect very soon. Birthdate is far more useful. However, that brings up another lurking question.

Really, do you even expect people to give you all of this information in the first place? That goes to the very heart of this proposed application. In this day and age, security is critical. If you capture information about young men and boys, and don't protect that information, you are opening yourself up to some serious liability. Do you know enough about security to build a safe web application?

Most databases evolve over time. You do your best to match the design up to your business rules from the beginning, but things can, and do change. The key is to build a solid design from the beginning so it doesn't require more changes than necessary down the road.

Case in point, you've characterized this as a "snapshot", no history required. If you design the tables around that architecture, you can't change your mind in a year and start tracking history until you re-architect the whole thing. That's a different question, though, from realizing that you need one more piece of information calling for a new field. That stuff happens all the time. Re-archictecting the whole database should be a rare thing.

George,

Thanks for the replies so far. I think we are making some progress at last!!




Isn't what you just described a "many-to-many" relationship with a junction table in the middle?

I think you just described...

member -||--------0<- response ->0---------||- question


And to clarify, are you saying do this just for my long, open-ended questions like "Why did you become a Boy Scout?" or are you saying do it for everything in the Member Profile on things like "Troop Name", "Location", "Interests", "About Me"?

(BTW, I don't want to break out each "Interest", so I just offer an "Interests" field which is like 80 characters.)




I know that. Bad example.




Again, bad example. I was going to use "Year Born".




I hope so...




First off, this is for Adults (i.e. over 25) only, who were/are involved in Scouting.

Secondly, yes, I am be mindful of security, and it is why I do things like "Year Born" versus "Date-of-Birth". Or why I say "Location" versus "Address, City, State, Zip".




Okay.




Fair enough.

Sincerely,


Rob

George,

Didn't hear back from you, so I wanted to clarify things...



Questions:
1.) You call that a One-to-Many Relationship with a Lookup, and I am calling it a Many-to-Many Relationship.

Can you please explain the difference??


2.) Is the design you are proposing only good for my open-ended questions (e.g. "What do you like most about Scouting?")?

Or can I use it for every Field/Attribute in a Member's Profile? (e.g. Troop #, Location, Occupation, Highest Rank, Scout Master)


3.) And if you say, "Yes", to my last question, then do I mix the Short-Answer Questions with the Long, Open-Ended Questions, or should they be in separate tables?

Thanks for your help so far!

Sincerely,


Rob

Careless of me to refer to it as one-to-many. Yes, when you combine foreign keys from two different tables in a single table to manage multiple records that way, it is a many-to-many relationship.

The design is good for all kinds of relationships. It's appropriate to this situation because you have a standard list of questions and you want each member to answer 0, one, or more of those questions. Many different questions can be answered by many different people.

"Or can I use it for every Field/Attribute in a Member's Profile? (e.g. Troop #, Location, Occupation, Highest Rank, Scout Master)"

Yes, of course, if that's what you want to do. But, did you not already state the business rule that you want a SNAPSHOT and that you don't want to track historical data? Really, that's what matters--your business rule.

If you want to track MULTIPLE Troop numbers for each member, or record multiple occupations for each member, etc., then yes, you use a junction table to do that. If you want to have a single Troop number for each member, then, no, you don't need the junction table. Again, not to pound to hard on this point, but your business rule determines what you do.

It really is based on your business rules, not some "theoretical" construct about database design. You have OPTIONS in designing tables; your choice among those options is really based on the applicable rule for each situation. Once you decide on what you want to store in the database, then you design tables to support that.

Why would you want to store "short" answers differently from "long" answers? That's the relevant question. If you mean by "short" answers that the user is selecting a choice from a drop-down list of choices, that is a different design from an answer that is open-ended and can consist of a single word up to a paragraph. But if you just mean that the answer only calls for a couple of words, then there's no reason to think of it differently.


And on the point of "getting back to you". Perhaps it's not clear from the introduction to the forums, but everyone here is a volunteer member. We spend time here when it is convenient, when there are interesting questions to answer, and when we need help ourselves. For that reason, you may find you have a short wait when someone who is online sees your question and thinks they can offer useful information. Sometimes, though, it may be quite a while in-between posts. Patience is always a good thing to keep handy.

Okay.




This is where I am getting confused...

Let's say there are 10 Members and 10 Questions, and we have agreed to use a Many-to-Many Relationship.

Just because I am using a Many-to-Many Relationship does NOT mean that I am storing multiple values or a history.

Member 1 and Question 1 come together to identify a unique Answer to Question 1 by Member 1. If Member 1 changes his response, then Answer 1 gets over-ridden.

So, if I want to store "Short Answers" like "Birth Year", "Troop #", "Location" then I would think that using the same Many-to-Many structure would work.

I mean, what is the difference between...

MemberID, Response, QuestionID
01, "My favorite Scouting adventure was when...", "What is your most memorable trip or adventure?"

and

MemberID, Response, QuestionID
01, "Green Bay, WI", "Location?"


The seem like identical situations to me. And in both cases, we are not storing any history of responses.

Likewise, in both cases, I could do all of this in the "Member" table like this...

MemberID, Location, FavAdventure
01, "Green Bay, WI", "My favorite Scouting adventure was when..."

Same thing, right?

Personally, I just think the Many-to-Many relationship seems more flexible because you are breaking at Attributes which describe a Member and not weighing down the Member table with hundreds of fields that might well change.

It is also much easier to have the Full Question stored using the M-to-M versus trying to come up with a Field Name for each Open-Ended Question?!





So my Business Rule is that I do not want to store a history of responses for any field at this time. That includes short fields like "Location" and long ones like "How can you help Scouting to continue in the 21st Century?"

Based on what I demonstrated above, it seems like you could accomplish that using one Member table, or a better way would be to use a Many-to-Many relationship, especially for my open-ended questions.




Because maybe you want to break out all Answers that are either varchar(1024) or text data-types like the Open-Ended Questions for performance and to prevent corruption?

Also, because "Birth Year", "Location", Troop Number", "Highest Rank" are logically different than questions like "How can you help Scouting to continue in the 21st Century?"




I was just saying that maybe my follow-up questions were unclear, so I rephrased them...

Sincerely,


Rob

"Member 1 and Question 1 come together to identify a unique Answer to Question 1 by Member 1. If Member 1 changes his response, then Answer 1 gets over-ridden."

Not necessarily. It depends on how you design the interface. However, if you let people CHANGE a response, then it's possible to change it. No big deal. You can design the interface so that people can't come back and change their previous answers. No big deal there either. That's another aspect of implementing your business rules. Do you permit changes to previously answered questions or do you not. Make the rule, design the interface to support the rule.

But, that's not what the Many-to-Many relationship is about anyway. It's about being able using the same Question for many different people.

Question 1 is, let's say, "Why did you join this group?"

One person can answer that question, twenty people can answer it, or no one needs to answer it. However, you do not repeat the question over and over. You store it, along with your other questions, in a "Question" table. The question table has two fields. One is the Primary Key. One is the text of the question. When you store the person's response, you refer to a question by its Primary Key value and you refer to the person by their Primary Key value. Finally, you store the response as text. If you want to change that response text, you can. Or you can prevent people from changing the response text. It's something you control through the interface.

And the same is true with regard to letting a person answer zero, one or many questions. You do not repeat the person's name over and over. You refer to the person by their Primary Key value, once for each question they answer.

"Personally, I just think the Many-to-Many relationship seems more flexible because you are breaking at Attributes which describe a Member and not weighing down the Member table with hundreds of fields that might well change.

It is also much easier to have the Full Question stored using the M-to-M versus trying to come up with a Field Name for each Open-Ended Question?!"

Congratulations! Those two statements show you have grasped a couple of the practical reasons we design tables the way we do, and why we don't treat them like spreadsheets.

You may or may not want to treat the short answer questions differently. IF, and this is the key here, IF you have a canned set of answers you offer as choices in a drop down, then you can treat them differently from open ended questions. But even then I'm not sure you'd bother, as discussed below.

However, if all of the questions, such as "Birth Year", "Location", Troop Number", "Highest Rank", etc. require a text box into which the person types one or more characters, then there is no logical difference between them and those questions that permit a lengthier response. At least not in terms of the database design. In that situation, a single character response is not conceptually different from a 10,000 character response. It is a difference in quantity, not quality. The LENGTH of a possible response is only relevant in the very trivial sense that you need to make the input box on the screen bigger for questions where you expect to have a longer answer. Another way to look at that would be to say that your interface design could encourage longer responses by presenting a very large input box, or encourage a shorter response by presenting a much smaller input box.

On the other hand, "birth year" is one of those attribues you could decide to handle either way. However, you know ahead of time that only specific values are going to be usable. I.e. Years. Given that fact, you COULD choose to present birth year as a drop down with years from, say 1910 to 2002. That guides the person to a valid choice. That's the interface consideration. You could still store the result of that choice in the same text field in the table. Even if it's only 4 digits long, and there is room for 1024 characters, nothing says you have to use all of the available space.

===========
"Didn't hear back from you, ...."

Actually, my point was that you didn't hear back right away for reasons other than clarity of your questions.

Yes, a User can go in an change anything in their profile except for there "username".




Right.




I am going to put my open-ended questions in a Many-to-Many as we discussed earlier.

As far as things like "Year Born" and "Location", I think those more logically fit in the "member" table along side things like...

- id
- username
- email
- password


However I see the point you were making, below...



Thanks for the thoughts!

Sincerely,


Rob

BTW, what do you call the field that holds the "question" in the "question" table and the "answer" in the "answer" table?

I was taught not to repeat your table name in field names.

So, you have...




and NOT...




I guess I was leaning towards...







Sincerely,


Rob

This post has been edited by robert_trace: Apr 1 2012, 11:15 PM

"BTW, what do you call the field that holds the "question" in the "question" table and the "answer" in the "answer" table?"

At the risk of sounding flippant, you can them whatever you want to call them. I might call them "QuestionText" and "Response" or "ResponseText" or something similar. You can probably think up good names, based on how you expect to use them. To be honest, to me, the more important considerations are that the names are meaningful and unambiguous and that they are not excessively long, and therefore hard to remember. What you don't want to have are names that are based on acronymns. "PITA" may mean something to the person who uses it daily in their work, but to someone else, seeing it for the first time, it means virtually nothing, or may even be misleading. And you don't want names with spaces or special characters in them.

Some highly experienced developers do use "ID" for the name of every ID field in every table on the theory that it is quick, easy and not confusing as long as you fully qualify every reference to those fields. It sounds like you are in this camp?

Some highly experienced developers use names like "MemberID" , or "QuestionID", etc. on the theory that naming the primary key to reflect the table in which it serves way makes it very clear what that field is regardless of where you see it. If this doesn't appeal to you, stick to your first inclination. You'll be in good company either way.

I like those better than just "question.question"!!




I think of tables and fields as table.field

PK's are always "id" and FK's are "<parent table>_id" so the name points back to the parent.





Sincerely,


Rob

Continued Success with your project.

George