In Meetup.com, whenever you join a meetup group, you're usually needed to accomplish an account for your particular group. For instance, should you join a film meetup group, you may want to list the genres of movies you like, etc.
I am creating a similar application, in which customers can join various groups and finish different profile particulars for every group. Assume the two options:
- Customers can make their very own groups and define what particulars to request customers that join that group (so, something a little dynamic -- possibly recommending that a minimum of an EAV design is needed)
- The developer decides now which groups to produce and specify what particulars to request customers who join that group (and therefore the profile particulars is going to be predefined and "hard coded" in to the system)
What's the easiest method to model such data?
More elaborate example:
The "Movie Goers" group request their people to specify the next:
- Birthdate (for use to compute member's age)
- Gender (must choose from "male" or "female")
- Favorite Genres (must choose 1 or even more from a listing of specified genres)
The "Extreme Sports" group request their member to specify the next:
- Description of Activities Loved (narrative form)
- Postal Code
The end result is that every group may need different particulars from people signing up for group. Ideally, I'd like anybody to produce a group (ala MeetUp.com). However, I additionally need a chance to query for people fairly well (e.g. find all ladies movie goers between your age range of 25 and 30).
For something similar to this....you'll want maximum normalization, which means you wouldn't have duplicate data anywhere. Since your user-defined tables might retain the same kind of record, I believe that you simply could go above 3NF with this.
My suggestion could be this - explode your tables to ensure that you've something near to 6NF with EAV, to ensure that each question that customers must answer may have its very own table. Then, your user-produced tables will all reference your question tables. This eliminates the duplication of information problem. (For example, you wouldn't want an entry within the "MovieGoers" group using the title "John Brown" and something within the "Extreme Sports" group using the title "Johnny B." for the similar user additionally you do not want his "what's your preferred color" response to be "Blue" in a single group and "Red-colored" in another. Data that may span across groups, like common questions, could be stabilized within this form.)
The primary downside of this really is that you would finish up with many different tables, and you'd most likely are thinking about creating sights for the record queries. However, when it comes to pure data integrity, this could work nicely.
Note you could most likely pull off only invoice discounting the common fields, should you wanted to. Good examples of common fields would come with Title, Location, Gender, yet others you might perform the same for common questions, like "what's your preferred color" or "have you got pets" or something like that to that particular extent. Group-specific questions that do not span across groups might be saved inside a separate table for your group, not-skyrocketed. I wouldn't advise this since it would not be as flexible because the pure 6NF option and also you risk duplication (how can you predetermine which questions will not be common questions?) but when you actually desired to, you could do this this.
There is a good question about 6NF here: Want to Understand 6NF by having an Example
Hopefully made some sense and that i hope it will help. For those who have any queries, leave a comment.
Really, this really is an issue that SQL isn't a right solution. Forget normalization. This really is the task for NoSQL document stores. Every user like a document, getting some essential fields like id, title, pwd etc. And each group adds possible ways to then add fields. Unique fields might have names group-id-prefixed, shared fields (that grasp more general concept) might have that area title free.
Except customers (and groups) then you'll have area explanations with title, type, possible values, ... also is excellent for any document store.
If you are using key-value document store right from the start, you will get this freeform chance of constructing your computer data plus querying them (though not by SQL, but through the means a NoSQL database provides).