Improving this table

database-design

Using Microsoft SQL Server Management Studio 2012

I need some help in improving my db structure.

Currently I have a table that looks kinda like this:

Id       Table_Name         Table_Id        Next_Id
1        Login              1               2
2        Logout             10              3
3        Close              2               4
4        Open               3               null
5        Login              4               null
6        Login              3               7 
7        Logout             4               8
8        Close              9               null
9        Login              2               10
10       Logout             3               null

Each row has a unique Id, a not null Table_Name, not null Table_Id and a Next_Id.

The idea behind this table is, that the user can add a "sequence" for my desktopapplication. If we look at Id 1 for example, we will see the Table_Name Login which contains the value Login and the Table_Id 1. My programm will now read the row in the Table Login that contains the Id 1
(Login will contain logininformation and some other stuff) and will afterwards look into the colum Next_Id to check if this is the end of the sequence. Since Next_Id contains the number 2 my programm will now read the row 2 of this table and will continue to do so until it hits the Next_Id value null

Is there any way I can improve my solution attempt in any way? If there are still questions regarding what I tried to do with this table then go ahead and ask 🙂

Best Answer

If I'm reading you right, the function of Next_Id is to group the entries into ordered sets. The way you've done it is the way an old fashioned network database would have done it. Each next_id "points to" another entry.

The relational model groups items into (unordered) sets by putting a common value in a foreign key. I don't have a model for your data, so I'll take a blind stab at a design. We use the Id of the first entry in the set to identify the group membership. The first entry doesn't have a null, instead, it points to itself.

Now, the table looks like this:

Id       Table_Name         Table_Id        Set_Id
1        Login              1               1
2        Logout             10              1
3        Close              2               1
4        Open               3               1
5        Login              4               5
6        Login              3               6
7        Logout             4               6
8        Close              9               6
9        Login              2               9
10       Logout             3               9

(I fixed the presumed bug in row 9).

This offers one advantage at storage time: The set membership is known when a new value is inserted. The next_id is not known, because the next event hasn't happened yet. This means you only have to store it once, instead of storing it once and then updating it when the Next _id changes from NULL to something else.

It also offers an advantage at retrieval time. Once you figured out the Set_id you are looking for, you can get all the members with a single trip to the database, using something like this:

select *
from mytable
where set_id = somevalue
order by Id

I have guessed at the correct order for the retrieved set, based on the idea that later events will have a higher id. But I don't know that that's true in your case.

If this select runs too slow, you could consider adding an index on Set_id.

A better solution would require knowing more about your data and your process. Hope this helps.

Related Question