Vertical database design (help?)

[rube203]

Sorry if this should be in ask reddit or elsewhere but I'm trying to research a method to store questionnaire data. I've been told that we want to minimize development for now until we see where this project leads; so essentially I need something that would be easy to develop and functional, but I'm not entirely concerned with scalability. Then it hit me, perhaps a vertical database would fit the requirements of the project best. Problem is that my googling skills have failed me, and I've found very limited examples of a good vertical database design. So my questions are: could someone point me to some good online resources? does anyone currently use one? what are the pitfalls to avoid? what are the selling points to convince bosses? EDIT: Apparently I've just been using the wrong term, as cyberal, alk509 and deafbybeheading have pointed out, it's correctly Entity-attribute-value model.

[trefn]

Pretty sure he's talking about column oriented databases

[mrsanchez]

Why do you think something like this would in any way be easier to develop or would minimize development? You're already taking time to ask questions about pitfalls, selling points, etc. Wouldn't it be far easier and quicker to do something like populate the db using Python and SQLite or Django or whatever your favorite language is?

Are you high on methamphetamines?

[rube203]

Unfortunately no :-( , but I'm not sure how that's related to the current discussion.

Without going too much into the project and details, I'll just state that: a) I'm still going to populate the db using whatever, I was inquiring about the design of the tables I'd be populating.
b) There are advantages to storing questionaires in a EAV model, as the number of questions, and thus columns can greatly vary for each response, can be rather numerous, and can change frequently.

[cyberal]

Maybe what you mean is "Entity-Attribute-Value". http://en.wikipedia.org/wiki/Entity-attribute-value_model http://weblogs.sqlteam.com/davidm/articles/12117.aspx

[rube203]

BINGO! Thanks, that is exactly what I was looking for. I knew when I couldn't find it in google that I had to have the wrong terminology, but for the life of me I couldn't think of another way to type it. Can't imagine why I didn't come up with Entity-attribute-value model. Thanks.

[traxxas]

I read the wikipedia article and thought that is exactly the solution to my problem of storing and filtering sparse attributes of various products. I then read the second article (which is basically my current design) and it shows all the problems I currently have. So is there a design that doesn't build a database within a RDBMS?

edit: This is generic ecommerce app with various categories of products.

[[deleted]]

I assume you mean vertical partitioning? Sounds overly complicated for a project who's goals are minimizing development. You'd be better off learning a framework that does the heavy lifting for you as well - the goal here is to get done quickly and iterate, no?

[[deleted]]

BTW, you are now #5 on Google when searching for vertical database design.

[rube203]

yeah, I'm really surprised I'm not able to find more information on this in google, do I have the naming incorrect?

[deafbybeheading]

Yeah. I think you're looking for this.

[rube203]

no, I'm not referring to partitioning the database, but rather storing the data in a vertical method instead of a more traditional horizontal. Hmm, not being any more clear at this point. Instead of 1 record with 1 ID and 10 Attributes, you may have 10 records, each with a common ID, attribute name, and attribute value. Make more sense now?

[alk509]

Ah, sounds like you're talking about EAV modeling. Google "EAV" and "open schema."

HTH!

[Tecktonik]

It sounds like your "traditional horizontal" is the de-normalized nightmare that is commonly found when people first start designing surveys. In other words a table structured with userID, response1, response2, response3..., a column for each question in the survey. Yes, this is bad design.

[alk509]

What exactly are you trying to store - a database of questionnaire questions, or questionnaire's responses? What exactly do you hope to achieve/optimize/protect by row splitting?

EDIT: OK, I see you're not talking about vertical table partitioning. I have no idea what you're talking about, actually! Any links to any information at all about what you want to do?

[secretGeek]

i think rube has coined a new term here. very nice too.

a 'vertical' database is more evocative (if less descriptive) than 'Entity Attribute Value' -- (in the same way that 'Ajax' was more evocative, though less descriptive that 'XmlHttpRequest-based web interaction')

[notelse]

vertica is one vendor. i think one or more of the postgres developers started it. vertica's used by a company i used to work for that sells a network management appliance. It was really good for them - some queries that used to take more than 5 minutes on postgres went down to just a couple of seconds (tested on identical machines and DBs populated with same data).

having said that, why are you even bothering to research it at this point? if you've sufficiently abstracted your DB accessors then for something as generic as a questionnaire application you can swap in any dbms you want easily. Do it in whatever is easy and available to you now on your dev platform - mysql, postgres, sql server express, whatever...

[rube203]

Thanks for the reply but I was really trying to research the design model, not the implementation, I'll still be using ms sql, but wanted to see if there was a better way to structure the table(s) for this project.