-2

I am designing a database for a new application.

For many tables, I have similar "general" attributes (ex: creationDate, targetDate, actualDate, author, ...).

Is it better to have those attributes in a separate table, and make a FK with all the tables that use those attributes or to have them in each table as additional attributes.

Is there a number of attributes/tables at which point an option is preferable to the other ?

eant123
  • 1
  • 1
  • Does this answer your question? [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Jun 29 '22 at 17:05
  • [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) Please before considering posting read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. SO/SE search is poor & literal & sometimes limited to titles, but read the help. Google re googling/searching, including in Q&A at [meta] & [meta.se]. – philipxy Jun 29 '22 at 17:06

1 Answers1

0

"Better" is a subjective term, so I will deal with a more objective problem-space, that is, the avoidance of inconsistencies and redundancies.

Now, let's assume that you have many tables:

  • t1
  • t2
  • ...
  • tn

all of which have some relatedness to the same creationDate, targetDate and actualDate fields.

Now, if there could be some rules according to which some fields accross tables have a functional dependency or even equivalence between two column set, then having these fields for both ti and tj where i <> j makes it redundant, which sooner-or-later can easily lead to inconsistencies.

So, to cope with this situation, in that case it makes sense to have a generic_attributes table and linking your records to them either via foreign keys or many-to-many tables.

Of course, these generic attributes can be created as

  • generic_attributes(id, att1, ..., attm)

or

  • generic_attributes(id, name) and
  • generic_attribute_values(id, generic_attribute_id, value)

but, if there are interrelatedness, particularly functional dependencies or conditional functional dependencies, or even functional equivalence, then a separate table for the attributes makes a lot of sense.

Otherwise, if these tables are not related at all, then you can choose either.

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175