2

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>>)

1 Answer 1

3

It sounds like you want three tables

  1. PROJECT( project_id, <<other attributes>>)
  2. LIST( list_id, project_id, <<other attributes (e.g. 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.

3
  • 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, 2012 at 21:35
  • To clarify: list_item( id, project_id, list_name, content )
    – bernk
    Jul 29, 2012 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. Jul 29, 2012 at 21:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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