Hey,

I've made a survey 'tool' where people can create surveys. All is working fine but I just wanted to check for my own sake what the best/better way is.

I'll keep it simple. The tables:

Survey. Defines a survey.
Sections. Splits in a survey.
Pages. Page breaks in a section. (sections can be a number of pages)
Question blocks. Blocks of questions. Grouping.
Questions. Individual question.

Now I could relate all those with a 1:m between them like:
http://www.dbforums.com/attachment.p...1&d=1226537332

Or I could have a bunch of FK's right through the table like:
http://www.dbforums.com/attachment.p...1&d=1226537403

I currently have it as the latter, a bunch of FK's. It was done this way as I was thinking it would make selection faster but due to the nature of the survey all data relating to a survey, including related tables is selected at once.

Am I making a lick of sense? Finding this difficult to put into words

Basically I want to know if I should just remove all the extra FK's and rely on the 1st diagram or should I keep with the second as selection will be faster.

The diagrams are snippets of the real erd, tried a textual representation of what I wanted to get across and well I'm no good with words (as you may have figured by now haha)

My 2c:

Usually it's your indexing that improves performance and not your FKs. FKs will actually slow down your insertion rate though this shouldn't matter in your application. Come to think of it I'd be surprised if selecting is all that slow either as the amount of data (questions) is quite small.

You could replace your survey, sections, page, question block and question tables with just one table that just had a type field to say whether it was a survey or a page etc. You would need to make the fields slightly more generic but it would make the application easier to write. The table would look something like :

Code:

question_structure: id, type_of_structure, parent_id, name, seq, instructions*, description*, help_id*, text.

I personally dislike FKs (but I know I'm alone in this opinion on this forum!) simply cause I don't like SQL to fail (say due to a FK restriction) as either the application tends to fall over (and a user gets upset) or the batch falls over (and some very brave sole rings me up in the middle of the night). I prefer to ensure that the batches and applications cannot put bad data into the system in the first place.

If you're trying to improve performance then you should be looking at your indexes. If you're just trying to build the system then I'd suggest going for the simplest table design that works and allows you to expand the system as required in the future.

Mike

Thanks Mike,

Well a bit of both really, I have made the system and am just about to write the CMS side of things when I thought to myself "damn you didn't plan any of this out at all you just ad hoc'd the whole thing... again!" so I'm trying to the 'right' thing.

So at present I have the DB done and all the logice to read the tables and display the survey. During the writing of that is when I thought I really don't need all those FK's everywhere.

My selections are dead simple IE:

Then:

Then:

Then:

Then:

All the FK's are indexed.

The logic is in PHP so all I'm doing is getting the records back as an array of objects, iterating over them and selecting again.

I understand your concern RE: FK failure but given the fact that these are created via a CMS there is a bunch of controls etc to capture these problems before they make it to the DB so it shouldn't be a problem.

famous last words

declare the FKs that you need for logical integrity, but don't declare transitive ones

for example (hypothetical) if you have states, counties, and cities, then county will have a FK to state, and city will have a FK to county -- you do not need to have a FK from city to state as well

Hah I was going to say '...it will be impossible to break them...' but never say never :P

Okay, thanks mate I'll bin them.