1 Topic by harri

  • harri
  • Member
  • Offline
  • From: Kristiansand, Norway
  • Registered: 2009-12-02
  • Posts: 23

Topic: Survey database structure?

Okay, here it goes.

I have created a very simple system for registration for events. It's so simple that the fields is hard coded in the database. This kinda worked fine when people were registration for a trip, as the fields required where the same. But as we found out that we could use it for other things, the hard coded fields were no longer enough. So now I am going to make a completely new system (with ZF of course, and doctrine) so that we can make whatever field we need for the registration. Make it completely dynamic, as it should be.

So that brings me to the problem at hand: I'm not quite sure how the database structure should be. I have several drafts but always seem to get stuck so now I need some help. I assume the structure would be very similar to how a survey database would be structured. Actually, the system will work much as Forms in Google Docs, but with additional features and will probably be base for a bigger system, with survey as a part of it. If I ever get that far;)

So her comes what I have got so far:

EVENT
  eventID
  eventName
  startDate
  endDate
  description
  maxAtt
  createdBy

FIELD
  fieldID
  fieldName
  fieldvalue
  required
  parent_field

ANSWER
  answerID
  fieldID
  content (the answer)

And then I have a USER entity but thats not a problem. The field and answer is the tricky part. The user here is administrativ users. Not registrants or people that answers the survey. We want it so that users don't need to registrate for be able to registrate for a event;)
If or when we see that we'll need that I will face that problem then.

I googled a bit before I posted here and found this one:
http://discuss.joelonsoftware.com/defau … .3.72177.9
Maybe the answer is there already?

Would be great if anyone of you had some ideas on how this should be done. smile

Last edited by harri (2009-12-14 05:02:40)

harri's Website

2 Reply by Jon Lebensold

  • Jon Lebensold
  • Administrator
  • Offline
  • Registered: 2009-06-27
  • Posts: 242

Re: Survey database structure?

Hi Harri,

it sounds like you've jumped the gun a bit here. I would start with either "user stories" or high-level use cases. Basically write out all the main ways that each use uses the system. This starts by figuring out the "actors" or "roles" in a system and what each actor will do with the system. Remember that you're doing something that's goal-oriented. Once you've established what data needs to be entered and how and when, it will be a lot easier to assess which relationships need to be managed in the db.

Things like a lookup table for FieldTypes come to mind (and a fieldtype_id in Field). however I'm assuming that you're answers will also be related to users somehow. You might want to create a Form table with a 1-many relationship to fields (and then define that relationship in a cross table (e.g. formId, fieldType, FieldId) and then have user answers be related to the form instead of each individual field.

these decisions will affect your object graphs in Doctrine too, e.g. $user->Answer->FormType->Form->Fields

or whatever.

Hope that helps.

3 Reply by harri

  • harri
  • Member
  • Offline
  • From: Kristiansand, Norway
  • Registered: 2009-12-02
  • Posts: 23

Re: Survey database structure?

I feel I have a clear picture of what the goal is, but this gave me something to think about, start with and try out. I'll let you know how it goes.
Thanks for the respons. smile

harri's Website