Thread: [Phpsurveyor-developers] Database Scheme LimeSurvey v2
The leading Open Source survey tool
Brought to you by:
c_schmitz
From: Carsten S. <car...@gm...> - 2007-05-09 22:30:40
Attachments:
Database-scheme-LimeSurvey2.png
|
Hi! In response to the thread about v2.0 lately and a conversation I had with Thibault this evening I thought it would be a good idea to show off the db design I did for v2.0 some time ago. (At that time i had already planned the language feature that is now in v1.0.) Alot of things are still missing but its a start. You can find this scheme in the subversion repository too.. Its made with mySQL Workbench Designer. As said.. its not complete but I will work on it in the next few days. My question is if anybody would like to lay out the object structure needed ? That person would not be alone. We would be discussing regularly about it and so make the best out of it. Anyone interested? regards Carsten |
From: Tom T. <tom...@he...> - 2007-05-10 03:06:02
|
Hey Carsten Yep I'm interested. I've also pretty well decided that I'm going to push ahead with RubySurveyor (RubyLimeSurveyor - better name anyone?) so it'll be interesting to see how cross-language the OO model can be. Cheers Tom -----Original Message----- From: php...@li... [mailto:php...@li...] On Behalf Of Carsten Schmitz Sent: Thursday, 10 May 2007 8:00 AM To: php...@li... Subject: [Phpsurveyor-developers] Database Scheme LimeSurvey v2 Hi! In response to the thread about v2.0 lately and a conversation I had with Thibault this evening I thought it would be a good idea to show off the db design I did for v2.0 some time ago. (At that time i had already planned the language feature that is now in v1.0.) Alot of things are still missing but its a start. You can find this scheme in the subversion repository too.. Its made with mySQL Workbench Designer. As said.. its not complete but I will work on it in the next few days. My question is if anybody would like to lay out the object structure needed ? That person would not be alone. We would be discussing regularly about it and so make the best out of it. Anyone interested? regards Carsten |
From: Carsten S. <car...@gm...> - 2007-05-10 16:16:54
|
Hey Tom, that sounds great. It would be nice if you could create some kind of document or use a free UML modelling tool and check you findings into the /phpsurveyor2 branch. (there is already an UML-File I created with ArgoUML . but my object model is uhhh not so good) I would ask you that you come regularly into the PHPSurveyor IRC channel (where I am most of the time) and we take a look together at it. What do you think? regards CArsten Tom Tuddenham wrote: > Hey Carsten > > Yep I'm interested. I've also pretty well decided that I'm going to push > ahead with RubySurveyor (RubyLimeSurveyor - better name anyone?) so > it'll be interesting to see how cross-language the OO model can be. > > Cheers > Tom > > -----Original Message----- > From: php...@li... > [mailto:php...@li...] On Behalf > Of Carsten Schmitz > Sent: Thursday, 10 May 2007 8:00 AM > To: php...@li... > Subject: [Phpsurveyor-developers] Database Scheme LimeSurvey v2 > > Hi! > > In response to the thread about v2.0 lately and a conversation I had > with Thibault this evening I thought it would be a good idea to show off > the db design I did for v2.0 some time ago. (At that time i had already > planned the language feature that is now in v1.0.) Alot of things are > still missing but its a start. > > You can find this scheme in the subversion repository too.. Its made > with mySQL Workbench Designer. > > As said.. its not complete but I will work on it in the next few days. > > My question is if anybody would like to lay out the object structure > needed ? > That person would not be alone. We would be discussing regularly about > it and so make the best out of it. > > Anyone interested? > > regards > > Carsten > > > > ------------------------------------------------------------------------- > This SF.net email is sponsored by DB2 Express > Download DB2 Express C - the FREE version of DB2 express and take > control of your XML. No limits. Just data. Click to get it now. > http://sourceforge.net/powerbar/db2/ > _______________________________________________ > PHPSurveyor-Developers mailing list > PHP...@li... > https://lists.sourceforge.net/lists/listinfo/phpsurveyor-developers > > |
From: Gregory N <gre...@gm...> - 2007-05-10 04:41:19
|
Great scheme indeed. The only thing I'd like to suggest is to have numbers (ID) as primary keys everywhere. Currently he "questiontemplates" tbl has only varchar. Will have to look further, though :-). Also, if some custom solution will require additional tables, the "native" tables should be kept (for the sake of compatibility). This is what I'm doing now, btw. Do you think there can be a universal/recommended way for adding these custom tables? As to object structure, well, I probably can participate, but don't feel myself familiar enough with PHPSurveyor yet to *start* this. On 5/10/07, Carsten Schmitz <car...@gm...> wrote: > > Hi! > > In response to the thread about v2.0 lately and a conversation I had > with Thibault this evening I thought it would be a good idea to show off > the db design I did for v2.0 some time ago. (At that time i had already > planned the language feature that is now in v1.0.) Alot of things are > still missing but its a start. > > You can find this scheme in the subversion repository too.. Its made > with mySQL Workbench Designer. > > As said.. its not complete but I will work on it in the next few days. > > My question is if anybody would like to lay out the object structure > needed ? > That person would not be alone. We would be discussing regularly about > it and so make the best out of it. > > Anyone interested? > > regards > > Carsten > > > > ------------------------------------------------------------------------- > This SF.net email is sponsored by DB2 Express > Download DB2 Express C - the FREE version of DB2 express and take > control of your XML. No limits. Just data. Click to get it now. > http://sourceforge.net/powerbar/db2/ > _______________________________________________ > PHPSurveyor-Developers mailing list > PHP...@li... > https://lists.sourceforge.net/lists/listinfo/phpsurveyor-developers > > > -- Regards, Gregory |
From: Gregory N <gre...@gm...> - 2007-05-10 04:58:12
|
Also, how about shortened prefixes for table fields? I usually prefer them for readability. For example, in "questiontemplates" table the first field can be named "qtmpl_name" instead of "questiontemplate_name" . -- Regards, Gregory |
From: Tom T. <tom...@he...> - 2007-05-10 05:33:50
|
Minor normalization issue with the survey_admin as a VARCHAR. I'm assuming this person should be a user in the system and can be represented by ID? =20 As a feature request, I'd like to look at extending the attribute system to allow for a whole bunch of abitrary meta-data to be associated with a survey. With the way we use PHPSurveyour we have information relating to each instance of a survey that we have to store in a separate table. Ultimately I'd like to be able to branch within a survey based on supplied meta-data (e.g. only ask question if attribute 'Gender' is 'M'). =20 Re field names, I'm a big fan of human readable field names. I take your point GreIf we want to shorten them I'd question why we need to prepend the table name on each field. The fields exist in a 'namespace' (ie. the table) that suitably qualifies each one. If there issues of ambiguity then we need to make sure reference the table in SQL. And if we're talking objects, questiontemplate.name makes more sense to me that questiontemplate.questiontemplate_name. =20 Cheers Tom =20 =20 ________________________________ From: php...@li... [mailto:php...@li...] On Behalf Of Gregory N Sent: Thursday, 10 May 2007 2:28 PM To: php...@li... Subject: Re: [Phpsurveyor-developers] Database Scheme LimeSurvey v2 Also, how about shortened prefixes for table fields? I usually prefer them for readability. For example, in "questiontemplates" table the first field can be named "qtmpl_name" instead of "questiontemplate_name" .=20 --=20 Regards,=20 Gregory=20 |
From: Gregory N <gre...@gm...> - 2007-05-10 11:01:05
|
Hi Tom, Minor normalization issue with the survey_admin as a VARCHAR. I'm > assuming this person should be a user in the system and can be > represented by ID? I agree. It should be foreign key to "users" table. (prefix_users.user_id in current scheme). As a feature request, I'd like to look at extending the attribute system > to allow for a whole bunch of abitrary meta-data to be associated with a > survey. With the way we use PHPSurveyour we have information relating to > each instance of a survey that we have to store in a separate table. > Ultimately I'd like to be able to branch within a survey based on > supplied meta-data (e.g. only ask question if attribute 'Gender' is > 'M'). Again, I confirm this. Right now I'm making the additional custom table to contain this metadata. I suppose it can be TEXT (to contain plain text or XML). Re field names, I'm a big fan of human readable field names. I take your > point GreIf we want to shorten them I'd question why we need to prepend > the table name on each field. The fields exist in a 'namespace' (ie. the > table) that suitably qualifies each one. If there issues of ambiguity > then we need to make sure reference the table in SQL. And if we're > talking objects, questiontemplate.name makes more sense to me that > questiontemplate.questiontemplate_name. The matter is we don't use *dot syntax* for simple queries like "select user_id from users...". But you're right again, it can be a way to go. -- Regards, Gregory |
From: Carsten S. <car...@gm...> - 2007-05-10 16:27:21
|
Hi Tom Tom Tuddenham wrote: > Minor normalization issue with the survey_admin as a VARCHAR. I'm > assuming this person should be a user in the system and can be > represented by ID? > > The survey admin is the name of the real survey admin (to show users), nothing else. The creator of the survey is in survey_owner_id. > As a feature request, I'd like to look at extending the attribute system > to allow for a whole bunch of abitrary meta-data to be associated with a > survey. With the way we use PHPSurveyour we have information relating to > each instance of a survey that we have to store in a separate table. > Ultimately I'd like to be able to branch within a survey based on > supplied meta-data (e.g. only ask question if attribute 'Gender' is > 'M'). Yes.. that will be the thing i will work on next.. There will be a great token system with unlimited attribute fields und token history (when was some token used in what survey) > Re field names, I'm a big fan of human readable field names. I take your > point GreIf we want to shorten them I'd question why we need to prepend > the table name on each field. The fields exist in a 'namespace' (ie. the > table) that suitably qualifies each one. If there issues of ambiguity > then we need to make sure reference the table in SQL. And if we're > talking objects, questiontemplate.name makes more sense to me that > questiontemplate.questiontemplate_name. The point is you wouldn't have to use the dots ;). Best regards Carsten |
From: Carsten S. <car...@gm...> - 2007-05-10 16:22:50
|
Hi! We could do that but if you start like that it gets confusing... since all other tables do use the full name Maybe if we shortcut the table name itself. regards Carsten Gregory N wrote: > Also, how about shortened prefixes for table fields? I usually prefer > them for readability. > For example, in "questiontemplates" table the first field can be named > "qtmpl_name" instead of "questiontemplate_name" . > > -- > Regards, > Gregory > ------------------------------------------------------------------------ > > ------------------------------------------------------------------------- > This SF.net email is sponsored by DB2 Express > Download DB2 Express C - the FREE version of DB2 express and take > control of your XML. No limits. Just data. Click to get it now. > http://sourceforge.net/powerbar/db2/ > ------------------------------------------------------------------------ > > _______________________________________________ > PHPSurveyor-Developers mailing list > PHP...@li... > https://lists.sourceforge.net/lists/listinfo/phpsurveyor-developers > |
From: Carsten S. <car...@gm...> - 2007-05-10 16:21:28
|
Hey Greg, you comment is noted.. I will complete the scheme... generally surveys will have an internal survey id but for external use there will be a GUID so it cant be guessed like now (still possible even that it is random) The common way as far as as i see is to add tables using the same naming convention. Plural names for he table name. Single names for fields as prefix. Example: table surveys field survey_id field survey_name .... I hope i understood you question right. Best regards Carsten Gregory N wrote: > Great scheme indeed. The only thing I'd like to suggest is to have > numbers (ID) as primary keys everywhere. Currently he > "questiontemplates" tbl has only varchar. Will have to look further, > though :-). > > Also, if some custom solution will require additional tables, the > "native" tables should be kept (for the sake of compatibility). This > is what I'm doing now, btw. > Do you think there can be a universal/recommended way for adding these > custom tables? > > As to object structure, well, I probably can participate, but don't > feel myself familiar enough with PHPSurveyor yet to *start* this. > > > On 5/10/07, * Carsten Schmitz* <car...@gm... > <mailto:car...@gm...>> wrote: > > Hi! > > In response to the thread about v2.0 lately and a conversation I had > with Thibault this evening I thought it would be a good idea to > show off > the db design I did for v2.0 some time ago. (At that time i had > already > planned the language feature that is now in v1.0.) Alot of things are > still missing but its a start. > > You can find this scheme in the subversion repository too.. Its made > with mySQL Workbench Designer. > > As said.. its not complete but I will work on it in the next few days. > > My question is if anybody would like to lay out the object structure > needed ? > That person would not be alone. We would be discussing regularly > about > it and so make the best out of it. > > Anyone interested? > > regards > > Carsten > > > > ------------------------------------------------------------------------- > This SF.net email is sponsored by DB2 Express > Download DB2 Express C - the FREE version of DB2 express and take > control of your XML. No limits. Just data. Click to get it now. > http://sourceforge.net/powerbar/db2/ > <http://sourceforge.net/powerbar/db2/> > _______________________________________________ > PHPSurveyor-Developers mailing list > PHP...@li... > <mailto:PHP...@li...> > https://lists.sourceforge.net/lists/listinfo/phpsurveyor-developers > > > > > > -- > Regards, > Gregory > ------------------------------------------------------------------------ > > ------------------------------------------------------------------------- > This SF.net email is sponsored by DB2 Express > Download DB2 Express C - the FREE version of DB2 express and take > control of your XML. No limits. Just data. Click to get it now. > http://sourceforge.net/powerbar/db2/ > ------------------------------------------------------------------------ > > _______________________________________________ > PHPSurveyor-Developers mailing list > PHP...@li... > https://lists.sourceforge.net/lists/listinfo/phpsurveyor-developers > |
From: Carsten S. <car...@gm...> - 2007-05-10 16:29:59
|
Hi Greg, Gregory N wrote: > Hi Tom, > > > Minor normalization issue with the survey_admin as a VARCHAR. I'm > assuming this person should be a user in the system and can be > represented by ID? > > > I agree. It should be foreign key to "users" table. > (prefix_users.user_id in current scheme). > Explained that in my previous mail. > > > As a feature request, I'd like to look at extending the attribute > system > to allow for a whole bunch of abitrary meta-data to be associated > with a > survey. With the way we use PHPSurveyour we have information > relating to > each instance of a survey that we have to store in a separate table. > Ultimately I'd like to be able to branch within a survey based on > supplied meta-data (e.g. only ask question if attribute 'Gender' is > 'M'). > > > Again, I confirm this. Right now I'm making the additional custom > table to contain this metadata. > I suppose it can be TEXT (to contain plain text or XML). > Yep.. its a feature that is planned too. > > > Re field names, I'm a big fan of human readable field names. I > take your > point GreIf we want to shorten them I'd question why we need to > prepend > the table name on each field. The fields exist in a 'namespace' > (ie. the > table) that suitably qualifies each one. If there issues of ambiguity > then we need to make sure reference the table in SQL. And if we're > talking objects, questiontemplate.name > <http://questiontemplate.name> makes more sense to me that > questiontemplate.questiontemplate_name . > > > The matter is we don't use *dot syntax* for simple queries like > "select user_id from users...". > But you're right again, it can be a way to go. > Point is we dont need dot syntax if the fieldnames are unique ;) Greg.. you too are always invited to come into IRC chat too and discuss things with the team and me and maybe even join the team for development Best regards Carsten |
From: Thibault Le M. <Thi...@su...> - 2007-05-12 07:52:43
|
Hi guys, We currently have 'attributes' for questions: this is very usefull =20 when we want to add small features without having to change the db =20 schema. Would it be possible to have such things for other objects (for =20 instance surveys, groups) ? ----- Message de car...@gm... --------- Date=A0: Thu, 10 May 2007 18:29:47 +0200 De=A0: Carsten Schmitz <car...@gm...> R=E9pondre =E0=A0: php...@li... Objet=A0: Re: [Phpsurveyor-developers] Database Scheme LimeSurvey v2 =C0=A0: php...@li... > Hi Greg, > > Gregory N wrote: >> Hi Tom, >> >> >> Minor normalization issue with the survey_admin as a VARCHAR. I'm >> assuming this person should be a user in the system and can be >> represented by ID? >> >> >> I agree. It should be foreign key to "users" table. >> (prefix_users.user_id in current scheme). >> > Explained that in my previous mail. >> >> >> As a feature request, I'd like to look at extending the attribute >> system >> to allow for a whole bunch of abitrary meta-data to be associated >> with a >> survey. With the way we use PHPSurveyour we have information >> relating to >> each instance of a survey that we have to store in a separate table. >> Ultimately I'd like to be able to branch within a survey based on >> supplied meta-data (e.g. only ask question if attribute 'Gender' is >> 'M'). >> >> >> Again, I confirm this. Right now I'm making the additional custom >> table to contain this metadata. >> I suppose it can be TEXT (to contain plain text or XML). >> > Yep.. its a feature that is planned too. > >> >> >> Re field names, I'm a big fan of human readable field names. I >> take your >> point GreIf we want to shorten them I'd question why we need to >> prepend >> the table name on each field. The fields exist in a 'namespace' >> (ie. the >> table) that suitably qualifies each one. If there issues of ambiguity >> then we need to make sure reference the table in SQL. And if we're >> talking objects, questiontemplate.name >> <http://questiontemplate.name> makes more sense to me that >> questiontemplate.questiontemplate_name . >> >> >> The matter is we don't use *dot syntax* for simple queries like >> "select user_id from users...". >> But you're right again, it can be a way to go. >> > Point is we dont need dot syntax if the fieldnames are unique ;) > > Greg.. you too are always invited to come into IRC chat too and discuss > things with the team and me and maybe even join the team for development > > Best regards > > Carsten > > ------------------------------------------------------------------------- > This SF.net email is sponsored by DB2 Express > Download DB2 Express C - the FREE version of DB2 express and take > control of your XML. No limits. Just data. Click to get it now. > http://sourceforge.net/powerbar/db2/ > _______________________________________________ > PHPSurveyor-Developers mailing list > PHP...@li... > https://lists.sourceforge.net/lists/listinfo/phpsurveyor-developers > ----- Fin du message de car...@gm... ----- |
From: Gregory N <gre...@gm...> - 2007-05-13 16:46:50
|
Hi, Yes, having some 'attributes' field could be useful. BTW, the matter I was talking about "unified recommendations for extending the database" is that previous developer has added a number of fields to existing tables (answers, users etc) instead of creating new tables. Not only it's incompatible, but also I have to write php code to parse his sql "exports" to insert survey in normal database. Such things happen :-) Thibault Le Meur wrote: > > We currently have 'attributes' for questions: this is very usefull > when we want to add small features without having to change the db > schema. > > Would it be possible to have such things for other objects (for > instance surveys, groups) ? -- Regards, Gregory |