Should I merge those tables into one table? Is there any tricky alternative?
Table Unit for a Template table:
Id (PK)
ParentId
Name
TemplateId (FK)
Table Unit2 for a Testplan table:
Id (PK)
ParentId
Name
TestplanId (FK)
Edit:
Why not make a table like that:
[UnitId] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[TemplateId] [int] NULL,
[TestplanId] [int] NULL,
[ParentId] [int] NULL,
Update2:
1 Template has N Unit
1 Template has N Testplan
1 Testplan has N Unit
These are the relations using one Unit table. But this could only work with a TemplateId AND TestplanId in the Unit table.
Following from comments in question:
Trying to reduce the number of tables is going to obfuscate their intended purposes. When you are trying to design a relational database of object, ask yourself two questions about each relationship. Is A a B? Does A have a B?
http://en.wikipedia.org/wiki/Is-a
http://en.wikipedia.org/wiki/Has-a
You've got a series of has-a relationships. (Specifically, has-many) Template has units. Template has testplans. Testplan has units (but, as you'll see, different units)
For each has-many relationships, you'll add a foreign key column to the property table, e.g., the unit would have a template_id.
Template
----------
id
<other columns>
TemplateUnit
----------
id
template_id (FK)
parent_id (FK) -- references same table?
name
<other columns>
Testplan
----------
id
template_id (FK)
<other columns>
TestplanUnit
---------
id
testplan_id (FK)
parent_id (FK)
name
<other columns>
With a schema like this, there's no confusion as to what tables and what columns relate to what other objects. This makes querying very easy as well.
SELECT *
FROM Template a
JOIN TemplateUnit b
ON a.id = b.template_id
JOIN Testplan c
ON a.id = c.template_id
JOIN TestplanUnit d
ON c.id = d.testplan_id
Now you know that all b columns are template units and all d columns are testplan units. Easy peasy.