Can you have 2 tables with identical structure in a good DB schema?

Question

2 tables:
- views
- downloads

Identical structure:
item_id, user_id, time

Should I be worried?

Answer 1

These tables are the same NOW but may schema change in the future. If they represent 2 different concepts it is good to keep them separate. What if you wanted to have a foreign key from another table to the downloads table but not the views table, if they were that same table you could not do this.

Answer 2

From a E/R modelling point of view I don't see a problem with that, as long as they represent two semantically different entities.

From an implementation point of view, it really depends on how you plan to query that data:

• If you plan to query those tables independently from each other, keeping them separate is a good choice
• If you plan to query those tables together (maybe with a UNION of a JOIN operation) you should consider storing them in a single table with a discriminator column to distinguish their type

When considering whether to consolidate them into a single table you should also take into account other factors like:

• The amount of data stored in each table
• The rate at which data grows in each table
• The ratio of read/write operations executed on each table

Answer 3

I don't think that there is a problem, per se.

When designing a DB there are lots of different parameters, and some (e.g.: performance) may take precedence.

Case in point: even if the structures (and I suppose indexing) are identical, maybe "views" has more records and will be accessed more often. This alone could be a good reasong not to burden it with records from the downloads.

Also, the fact that they are indentical now does not mean they will be in the future: views and downloads are different, after all, so sooner or later one of both could grow an extra field or two.

Answer 4

I think the answer has to be "it depends". As someone else pointed out, if the schema of one or both tables is likely to evolve then no. I can think of other cases well (simplifying the security model by allow apps/users access to one or the other).

Having said this, I work with a legacy DB where this is a problem. We have multiple identical tables for customer invoices. Data is actually moved between then at different stages in the processing life-cycle. It makes for a complicated mess when trying to access data. It would have been easily solved by a state flag in the original schema, but we now have 20+ years of code written against the multi-table version.

Short answer: depends on why they are the same schema :).

Answer 5

I would say that having identical tables within your schema would be a design smell, but it's possible this might occur if the tables have different semantic meaning. In your case, it sounds like the item_id refers to the same entity, therefore I'd say the views and downloads should be represented as columns on a single table. I'm sure there is some specific normal form that a DBA could cite to objectively answer this question.

Answer 6

Chris Date and Dave McGoveran formalised the "Principle of Orthogonal Design". Roughly speaking it means that in database design you should avoid the possibility of allowing the same tuple in two different relvars. The aim being to avoid certain types of redundancy and ambiguity that could result.

Arguably it isn't always totally practical to do that and it isn't necessarily clear cut exactly when the principle is being broken. However, I do think it's a good guiding rule, if only because it avoids the problem of duplicate logic in data access code or constraints, i.e. it's a good DRY principle. Avoid having tables with potentially overlapping meanings unless there is some database constraint that prevents duplication between them.

Answer 7

It depends on the context - what is a View and what is a Download? Does a Download imply a View (how else would it be downloaded)?

It's possible that you have well-defined, separate concepts there - but it is a smell I'd want to investigate further. It seems likely that a View and a Download are related somehow, but your model doesn't show anything.

Answer 8

Are you saying that both tables have an 'item_id' Primary Key? In this case, the fields have the same name, but do not have the same meaning. One is a 'view_id', and the other one is a 'download_id'. You should rename your fields consequently to avoid this kind of misunderstanding.