The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Help:: DB Schema Design

Hi there

I am trying to figure out the best way of representing something like this in a database schema.

Imagine a shopping list

A shopping list can be made up of individual items.
It can also be made up of other shopping lists.

For Example

Son's birthday list
1. bike
2. new trainers
3. x-box

Grocery list
1. Bread
2. Milk
3. Butter

Mom's Shopping list
1. Son's list
2. Grocery list
3. make-up kit
4. jimmy choo shoes

I would like to pull up mom's list, and be able to view the expanded contents of 1 and 2.
btw, 1 and 2 could themselves be made up of an aggregation of lists.

Cheers for the help in advance.
Oldie but currently braindead Send private email
Tuesday, February 14, 2006
revert my buffer
Tuesday, February 14, 2006
cheers for the link.

Trees are a beginiing, but do not go far enough, as they assume that there is a top node to the tree, and that the levels are predefined.

In the example above, I might want to create a Dad's list, which includes the mom's list, and maybe a workshop_items list.

Similarly, the Dad's list might just include the Son's List, and his Workshop_Items list.
Oldie but currently braindead Send private email
Tuesday, February 14, 2006

I assume what you've given is an example, not the real app ...?

Is there anything that distinguishes a list from a listitem?

Could you not use something very simple like:

table things
  int id; // key
  text description;
  int parent_id; // references another id in this list

for a thing that is not in another list, parent_id is null
revert my buffer
Tuesday, February 14, 2006

Cheers for the help so far. It is getting me thinking. Your assumption is that item can only have one parent.(I think)

I am saying

I want to be a able to display a shopping list where

1. Item is base unit
2. Item can belong to one or more shopping lists
3. Lists can have one or more items (many to many already)
4. Lists can belong to one or more lists
Oldie but currently braindead Send private email
Tuesday, February 14, 2006
no probs.

Dunno really what you mean by "item is a base unit".

True that what I suggest doesn't give you many-many.

But if parent_id was a list of ints (or set or something) that would do it? I think mysql can do that fine.

But no doubt anytime now some database supremo will chime in and give us the *proper* way to do it ... ;)
revert my buffer
Tuesday, February 14, 2006
I think I need to sleep on this problem a bit. Here is an english version of the problem

Your family members have wish lists on amazon. Each list contains one or more books

You then create a couple more lists
Siblings - a list made up of the lists of your siblings
Parents - a list made up of your parents' lists
Aunts - a list made up of your aunts lists

I need an easy way to show a list of lists - including the lists made up of other lists

Your wife decides to do the shopping for you. She creates a new list
She adds your parents' list
She adds your siblings' list
She adds a couple more lists
She also adds a few cookery books in there.

The easy way would be to copy the elements of the constituent lists when creating a new list.
The problem with this is that if your brother removes a book from his list, it is not propagated, and your wife could end up buying a book he no longer needs!

This is sounding more and more like a BOM problem. My head hurts :-)
Oldie but currently braindead Send private email
Tuesday, February 14, 2006
IMHO obviously it's not a tree, it's an oriented graph.
The table proposed by revert would work, the 'thing' will just have more then one parent id. I would add some list/thing bit.
Or, make two tables, lists and things, joined it.
Tuesday, February 14, 2006
here is an example of the table that does what you describe:

id    description    parent_ids
1      "a book"        3
2      "nother book"  3, 6
3      "my list"      6
4      "boring book"  6
5      "read this"    6
6      "your list"    NULL

so "my list" contains "a book" and "nother book"
"your list" contains "nother book", "mylist", "boring book" and "read this"
revert my buffer
Tuesday, February 14, 2006
list ::= list_item*
list_item ::= (an_item|list)+
an_item ::= any non-list item

list_table:: list_id(PK) NUMBER -- a unique id
            list_owner (FK?) VARCHAR -- name of Owner, or
                                      ref to owner_table?
            list_desc VARCHAR2 -- description/title of list
            list_item (FK) NUMBER -- ref to an_item or

an_item_table:: an_item_id (PK) NUMBER -- unique id
                an_item_desc VARCHAR2 -- description/title of
                                      an item

Just off the top of my head...
Honu Send private email
Tuesday, February 14, 2006
This article seems to be the closest

(second example)
Oldie but currently braindead Send private email
Tuesday, February 14, 2006
I think I have found a solution, of sorts
Copied from Google Groups, circa 1995

You need two tables, one of which will represent all ingredients, and the other of which will represent the relationships between those ingredients.  The ingredients table will actually contain end products (finished goods) in addition to actual ingredients (sub-assemblies, components, and raw materials).  The two tables could be:

    material_id (Primary Key)

    parent_material_id (Primary Key)
    child_material_id (Primary Key)
    ingredient_sequence (Primary Key)

To represent a sponge cake and a glue you might have:

In the material table:
    6,"Victoria Sponge Cake"

In the ingredient table:

The ingredient_sequence is provided in case you want to include one ingredient multiple times in the same recipe. It would be usual to create two indexes on the ingredient table -- the primary key, and a secondary index which consists of child_material_id, parent_material_id, ingredient_sequence.

If you want to add another 'finished product', for example, a 'double sponge cake', simply add another entry to the material table, adn link its components to it using records in the ingredient table, e.g.,

To the material table:
    8,"Double sponge with icing"
  10,"Icing powder"

To the ingredient table:

Instructions for making the finished products and sub-assemblies would be attached to the material table, either as a sequence of steps in another table, or as a text entry in the material table itself

The last sentence is what I need to investigate, as that is what will determine the read speed on the database.
Oldie but currently braindead Send private email
Tuesday, February 14, 2006
Apparently this book is a good place to begin for anyone else who might have a similare DB problem to solve

Joe Celko's Trees and Hierarchies in SQL for Smarties
Oldie but currently braindead Send private email
Tuesday, February 14, 2006
special thanks to you all, particularly revert :-)
Oldie but currently braindead Send private email
Tuesday, February 14, 2006
That sounds very similar to the Composite pattern.  You might wanna check it out...
My2Cents Send private email
Tuesday, February 14, 2006

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz