Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The app I'm working on revolves around projects. Each project can contain several lists, and each list is made up of several items. Each list relates to a single project.

Is it better to have one listitem table structured as such: id, list_id, event_id, content or 2 tables; one containing the relationship between project and list, and the other containing only list items and their relationship to a particular list?

Is it better to have just one table of which each row would be a list item, including that item's relationship to a project and the name of the list it belongs to:

list_item(id, project_id, list_name, content, <<other attributes>>)

or, have one list item table for all of the list items in the app and and a separate list table:

list(id, project_id, name, description, <<other attributes>>)
list_item(id, list_id, content, <<other attributes>>)
share|improve this question
add comment

1 Answer

It sounds like you want three tables

  1. PROJECT( project_id, <<other attributes>>)
  2. LIST( list_id, project_id, <<other attributes (i.e. the name of the list)>> )
  3. LIST_ITEMS( list_item_id, list_id, <<other attributes>>)

Combining the LIST and LIST_ITEM tables wouldn't be appropriate because then you'd be storing attributes of the list (i.e. the list's name and description) in multiple rows of the LIST_ITEM table rather than just once in a single row of the LIST table.

share|improve this answer
    
Thank you, correction made. The project table was a given, there are actually many more tables for other functionality but as the question deals specifically with the list aspect I only mentioned those. Why do you suggest to have a table containing the relationship between projects and lists? Is it not more efficient to store the id of the parent project directly in the LIST table? A list can only belong to one project, and a list item can only belong to one list – therefore it is technically possible to use just 1 table. My question is basically why I should NOT do it this way. –  bernk Jul 29 '12 at 21:35
    
To clarify: list_item( id, project_id, list_name, content ) –  bernk Jul 29 '12 at 21:36
    
@bernk - If each list relates to a single project, then I would get rid of the project_list table and add the project_id to the LIST table. It seems odd that you wouldn't want to potentially reuse a list across multiple projects though-- it seems like that would force users to do a lot of redundant data entry. –  Justin Cave Jul 29 '12 at 21:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.