Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I am looking for a generic term, e.g. for a database abstraction, that includes all tabular data structures like database tables, views, tabular query results aso.
As far as I understand, 'entity' is not the proper term since this would correspond to a table but not a view or even a query. 'Result' would be counterintuitive on modifiable data structures.
Which would be the proper term in the professional environment?
A "thing with columns and rows" is a relation. This is the word Codd used in his seminal paper, starting the RDBM revolution.
A "thing that can change but retain its identity" - is an entity. A single row of a relation represents an entity when you can find a unique key representing it. If you have two non-unique columns: event and date - rows will not form entities, because changing either attribute changes the meaning of the row completely. If you add an additional column (like id) or if you make date unique, you will get rows that work as entities (no matter whether it's a view or a table, or a result of a join).
A database object is just that: a thing managed by your database server. It could be a table or a view, but it could also be a user or a stored procedure.
Result is the name for the part of the API (e.g. JDBC) that reprents your results (possibly a relation or a cursor) in some other programming environment (e.g. Java runtime).
But the truth is - in a real professional environment, anything goes. People get those words mixed up and generally don't care. Heck, I don't care.
Postgres (and also relational algebra) calls them relations - tabular things of rows and columns that you can query from. This includes tables, views, materialised views, temporary tables, or even temporary query results.
I would avoid the term "database object" if you really mean relations only, since "database objects" would also include schemas, indexes, users, sequences, policies, column definitions, types, functions, and many more. Database objects are the objects that are manipulated with DDL, whereas (the data within) relations are manipulated with DML.
Going back to the basics, a table is a set of records. A view is a virtual table, either calculated or materialised, which means it is still just a set of records.
The Microsoft documentation uses <table_source> in the BNF to indicate any source of rows - a table or view or one of a number of table-valued functions.
I've used this term in class for many years without confusion.
Most of the time, the people you are talking to don't understand, or care, about the difference between a table or a view. Rightly so - all they need to know is "where do I get the data to do my job?"
Just explain, "here is a list of tables, pick the one you need." Of course, you haven't named tables and views differently with some stupid tibbling, right…?
How about Tabular? It implies "tabularity" which means it works like a table and has the characteristics of a table but is not necessarily a table.
From Techopedia
Database Object
A database object in a relational database is a data structure used to either store or reference data. The most common object that people interact with is the table. Other objects are indexes, stored procedures, sequences, views and many more.
When a database object is created, a new object type cannot be created because all the various object types created are restricted by the very nature, or source code, of the relational database model being used, such as Oracle, SQL Server or Access. What is being created is instances of the objects, such as a new table, an index on that table or a view on the same table.
Additional reference...
I think the top answer for the term "relation" is great, especially when talking about databases in theory. But I think isn't as fitting when talking about an actual database system implementation, in practice. The reasoning being is there's a lot of constructs that exist in an actual database system implementation and which vary across implementations that aren't discussed or covered in database theory. Examples of this may include Stored Procedures, Table Variables, Table-Valued Functions, OPENQUERY() (in the Microsoft SQL Server implementation), CTEs, Subqueries, etc.
As evident in the many discussions throughout this Post, it seems different people have different interpretations of what additional physical constructs they consider to be a relation, but database theory isn't aware of those constructs. I think OP's question is quite broad, which elicits the wide array of responses displayed here, but I think most of them are fair dependent on the context in which you want to speak in.
That being said, this is why I believe the following to also be correct answers when talking about those constructs in a physical form. When a Table exists in a database system, it is concrete, considerably it's an instance of an object like a collection object of data in a running application, here the database system being the running application. Other physically implemented constructs that can return "tabular data" (as worded by the OP), e.g. Table-Valued Functions or Table Variables can also be considered objects, when referring to them in the context of an actual database system implementation. I can talk about these constructs disjoined from the set of data (relations) they can hold / return and focus on their own existence as physical objects, if that's the context I choose to speak about them.
This is why I like Brendan's answer for the term "Database Object". I also wouldn't rule the word "entity" out either.
ORMs, such as Microsoft's Entity Framework, consider all database objects (Tables, Views, Stored Procedures, etc) as entities, I believe. This is in the context of the models that structurally represent the object (typically as a class) before it's actually instantiated.
So personally, I like to use the term entity when I'm speaking of a logical concept before instantiation of sed model, or further upstream, when a logical schema is being defined such as through a database diagram. I think entity is a good term to describe the structure of a thing without it needing to be physically realized.
But when talking about a concrete representation, such as an instance of a Table object in the application, or the concretely compiled Table in the database layer, then I think database object is a good term to use just as well.
