Group rows in a table - design

Question

I have a table TreeStructures wich holds structures of Trees. This table has columns 'id, name, left, right and a foreign key TreeId' to a table Trees with a column 'id' and some more information about the Tree.

Now, if 'id' was the only column in table Trees, would it be good to remove the table Trees and let the foreign key TreeId just be a column?

Making a new Tree would mean that I would have to check the highest value of TreeId and then increment that by one to get a new value. There's a danger of concurrency here...

Anyway, what's best practices in this situation?

Answer 1

I think that for future use - leave the tree table.

maybe you'll need in the future a tree name ?

it can work like you said, but it is no recommended.

also - if the table will get very very big... it will be very inefficient.

Answer 2

Difficult to say without a solid example, but it sounds reasonable. I'm just not sure what the name in the current schema refers to, and why it relates to the TreeStructure rather than the Tree.

(Personally I'm quite happy in general to mix nested set structural information into a data table anyway.)

Answer 3

the Tree table is a distinct entity, and deserves its own table even if the table is "all key". There may or may not be additional columns later, but omitting this table leaves your database denormalized, which is always a potential danger to data integrity

note that the concurrency issue you're concerned about is a no-brainer on most modern databases (use an auto-incremented identity type for the ID column), and would actually be worse without the Tree table, because you'd have to do a MAX(ID) scan on the whole leaf table