-1

I have different entities but, ironically, in same time they are very similar, and I wonder if I can save them in one united table.

I need four tables for Departments, Committees, Groups, and Bands in which columns for department table should be (Name,Code), Committees should be (Title,ID) , and Group (Description,Num) , and Bands (symbol,Figures)

I thought to make a united table in which there is another field (Column) to hold the row-type, and make columns name (FLD01,FLD02,FLD03,FLD04, etc). and make anther table to hold mapping

Departments FLD01   Name
Departments FLD02   Code
Committees  FLD01   Title
Committees  FLD02   ID
Groups      FLD01   Description
Groups      FLD02   Num
Bands       FLD01   symbol
Bands       FLD02   Figures

I retrieve data with SQL and wonder if there is a way to join that united table to this table so I can automatically get columns names instead of FLD01, FLD02..etc. be informed that I will retrieve only on type (department, band, ...) at a time..

APC
  • 144,005
  • 19
  • 170
  • 281
DragonFire
  • 17
  • 4
  • 2
    I would say that most likely, combining them into a single table is a bad idea. These are different *entities* that may have different behavior and different relationships between them and between them and other entities. Relationships you may not be able to foresee today, that may happen in the future. It seems your strategy is solely based on merging them based on the attributes they have, and you are not looking at their existing and/or potential behavior. – The Impaler Aug 22 '23 at 14:06
  • 1
    Agree, this isn't a good idea. Keeping separate entities allows you to define relationships more meaningfully, makes SQL easier, allows for better integrity enforcement, etc.. – Paul W Aug 22 '23 at 15:19
  • 1
    What you are describing is the Entity-Attribute-Value data model, which is a well known, indeed notorious, Anti-Pattern. Don't do it. It's just a world of pain. – APC Aug 22 '23 at 16:08
  • 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 Aug 22 '23 at 16:29
  • [How can you represent inheritance in a database?](https://stackoverflow.com/q/3579079/3404097) [More](https://stackoverflow.com/q/190296/3404097) [How to design a product table for many kinds of product where each product has many parameters](https://stackoverflow.com/q/695752/3404097) [More](https://stackoverflow.com/a/2945124/3404097) [And more](https://stackoverflow.com/q/5106335/3404097) [Re EAV](https://stackoverflow.com/a/23950836/3404097) etc etc etc [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] – philipxy Aug 22 '23 at 16:30

1 Answers1

1

In theory, sure, you could write a dynamic pipelined table function and always retrieve the data via this pipelined table function.

Practically, if the similarity is that all these entities have the same number and type of columns but the columns have different names, it seems very unlikely that it makes sense from a data model standpoint to combine them.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384