How to design a table for the following requirement?

Question

Hi all,

I have the following requirement.

Parent child mapping table

ID ParentID ChildID

1 1 2

2 1 3

3 2 4

3 2 5

3 4 6

Main Table

ID  ViewCount

1   3  
2   4    
3   4 
4   5   
5   6

The Parent and childs are present in the same table itself and having their own viewcounts.

Whne I read any record it has to get the particular records viewcount as well as their child's view count.

I have updated only the particular records viewcount, not updated their parents.

So from the main table, When I access the record with ID 1, it has to get all of their childs viewcount and its own viewcount. for this case 22.

when i access 2 the viewcount is 15.There is no level into that, it will grow in accordance with childs.

How to write a query or redesign the table or anything

to implement the above requirement?

The ID column present in the main table refers both Parent and child ID's and its the primary key also. In the mapping table we are mapping the parent & child relationship. ID present in the parent/child table is table's primary key field.Right there is no use of that field

Answer 1

The structure you have (parent/child) is called Adjacency List. Getting a node and all its children (for example to sum their ViewCounts requires recursion. Don't hold your breath waiting for MySQL to implement it: http://bugs.mysql.com/bug.php?id=16244. The SQL standard feature, CTEs, is implemented with almost identical syntax by most major SQL databases (PostgreSQL, DB2, MS SQL Server, possibly recent Oracle); Oracle has a CONNECT BY / PRIOR.

There are other encodings of trees that can be queried without recursion, but they're somewhat more complicated than Adjacency List. Google "sql nested intervals", or read Vadim Tropashko's book

Oh and BTW, that synthetic id column in your parent/child table is terrible design since it allows you to have duplicate data. Drop it, and create primary key over the two columns that really belong there.

Answer 2

In your program do you have any objects that represent the parent and child items? If so you can do the recursion in the program to get a list of Item IDs then you can do the following.

SELECT sum(ViewCount) FROM MainTable WHERE id in (List from program)