Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2006
    Posts
    9

    More links (fk's) the better?

    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)
    Attached Thumbnails Attached Thumbnails more.jpg   less.jpg  

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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

  3. #3
    Join Date
    Apr 2006
    Posts
    9
    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:

    Code:
    SELECT * FROM survey WHERE id = val
    Then:

    Code:
    SELECT * FROM sections WHERE survey_id = survey.id
    Then:

    Code:
    SELECT * FROM page WHERE survey_id = survey.id AND section_id = sections.id
    Then:

    Code:
    SELECT * FROM question_blocks WHERE survey_id = survey.id AND section_id = sections.id AND page_id = page.id
    Then:

    Code:
    SELECT * FROM questions WHERE survey_id = survey.id AND section_id = sections.id AND page_id = page.id AND block_id = question_blocks.id
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Hayce
    ... 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2006
    Posts
    9
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •