0

Would you introduce table inheritance for the TemplateTeststep and TestplanTeststep tables?

The TestplanTeststep is/will be always a readonly copy of the TemplateTeststep (red) PLUS some editable fields (purple).

The TemplateTeststep will have individual fields which will never appear in the TestplanTeststep table. The TestplanTeststep will have individual fields which will never appear in the TemplateTeststep table.

Moreover the TestplanTeststep table has some fields from the TemplateTeststep table which are read-only. (nobody should change them because they need to be safe for reports/investigation etc...)

enter image description here

The TemplateTeststep has still the fields ModifiedBy and ModifiedAt which does not appear on a TestplanTeststep for historical tracking who changed what/when.

Elisabeth
  • 20,496
  • 52
  • 200
  • 321

2 Answers2

0

This really depends on the context of use.

Is speed a big concern? If there are a large number of reads of these data, splitting the values for a single logical entity across two tables will increase latency on reads. In a low-throughput system this will be negligible, but if you've got hundreds of millions of rows then this would add up.

Is constraining the size of the dataset more important than read-access performance? It's no secret that denormalising data often makes them quicker to read (they're only stored in one contiguous space, instead of on different sectors of the disk) and reduces the amount of locking required, but will add to the amount of space to store the entire dataset.

Are you using an ORM solution, or will you be joining across the tables 'by hand'? The former may make splitting the data trivial, and the latter may be more error-prone.

I don't think there's a right or wrong answer here; I'd be inclined to use whichever approach is used in the rest of the codebase/the rest of your organisation.

For what it's worth, I'd probably use multi-table inheritance, as I tend to use Java's Hibernate for ORM.

DeejUK
  • 12,891
  • 19
  • 89
  • 169
0

  1. You should have a Foreign Key in the TestPlanTestStep Table.

  2. The Primary-Foreign key Relation-Ship of TemplateTestStep and TestPlanTestStep to follow Second form of Normalization.

  3. The Design is like De-Normalization as if somebody needs reports. If not so please remove Duplicate columns to avoid redundancy in Database.

Pankaj
  • 9,749
  • 32
  • 139
  • 283
  • 1. There is a foreign key. 3. If someone delete a template all units,steps are gone. But no those of a testplan. This is wanted replication/backup of data. – Elisabeth Jun 24 '12 at 11:44
  • are you making sure that you have relation between 'TemplateTestStep' and `TestPlanTestStep` table? – Pankaj Jun 24 '12 at 14:13
  • Then there is a misunderstanding. There is no relation in form of a foreign key between the tables TemplateTeststep and TestplanTeststep. Why did you assume there should be? – Elisabeth Jun 24 '12 at 17:29