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
(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
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 🙂
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.