Table View Distinction Archaic

There seems to be somewhat of a consensus in topics/debates that grew out of FearOfAddingTables that the concepts of and difference between "table" and "view" probably need to be updated.

In most current RDBMS, an actual (base) column must belong to one and only one table. However, it can belong to multiple views. This one-and-only-one criteria is perhaps an unnecessary restriction. It is true that a column may need to belong to at least one "table" in order to have a reference point to create views from. Basically, this rule ties it to at least one key; otherwise it is sitting out in space by itself. Perhaps we need "key groups" instead of tables, whereby any given value column must belong to at least one key-group.

Or perhaps "views" need to be composed of "tables" instead of columns. In this approach, the "tables" would be "thin" tables that associate one or few values to keys, and then the views would be composed of these thin tables.

Existing Way:

  ViewX = column1, column2, column3, etc... FROM wideTable1, wideTable2 ...

New Way:

  ViewX = thinTable1, thinTable2, thinTable3

Some mechanism needs to be in place to make sure the keys are compatible here. This is what hints back to the incomplete "key group" concept above.

Food for thought...

--top

Sounds like you are confusing an application with the database. The application can make whatever views it wants - the application that views the databases should not directly affect the relational design that the developer/engineer decides upon. Normalized tables are not thin to display data to the end user using the application; the purpose of normalized tables are to organize relational data that can be queried sensibly (instead of ridiculously all shoved in a unrelated vast wide table). What you select (query) from the tables and display (the view) to an end user should not affect the storage of the relational data. If a key is in several tables, then a key is in several tables logically. I do not understand what the hell you are going on about. It is possible, that your users are directly accessing your databases instead of you creating applications to access them. I'm still not quite sure I understand what the flying fig you are talking about.

I am not clear on what you mean. What do you mean by "unrelated"? Entity stuff is related to the entity. And, update-able app-specific views would be a nice addition also. Add that to the wish-list. Thanks for pointing that out (even if it was not your intention).

One thing I am trying to achieve here is OnceAndOnlyOnce when a new column is added. The current approach requires it be added to both the base table and possibly many related views. If views were defined in terms of tables instead of columns, we could reduce the NewColumnShuffle?.

--top

{I'd aim for View == Query + Source Metadata (enough to backtrack as many columns as possible for editing).}

I mean the program that views your database can do anything you want, so I don't see how this has to affect the thin and wide tables (whatever those really are).

{I don't disagree (to within the limits of security and the proper context for 'anything'), though I'd note that heavy use of thin tables might be impractical without good viewing software. And a definition of wide vs. narrow tables is provided in NormalizationRepetitionAndFlexibility.}

Wide tables and thin tables (relvars) are the database itself, not the view of the database. One can make a viewer/editor that allows all the tables to be edited as one wide table if he wants... in a program like a query browser. i.e. why force the database to map to the application? Why not design the database relatively (using relational techniques) and just make the query browser and table editor do anything you want? There is no need to muck up the logical design of a database to have a view! In other words, we already know what the differences between table and view. I am worried you think that the logical design of the database is directly related to the view.

{All data in one table might be a tad non-intuitive when dealing with one->many and many<->many relationships, whereby editing one column entry changes dozens or hundreds of others simultaneously. While one can make a viewer/editor that allows all tables to be edited as one wide table, I think it would violate certain concerns that are related to HCIs,unless one was really careful. Maybe if you pushed it all the way back to 0NF with multi-valued columns, it would work. Anyhow, I fully agree with your point - the view is whatever we wish to make of it. Top seems to be overly concerned that his favored view-description-language may be difficult to utilize and maintain (in the cost-of-change sense) given use of narrow tables. There are two points one can consider relative to that - one is that it is perfectly likely that views can and will be constructed automatically due to use of meta-data, and the second is that hand-built views only cost as much to maintain as any other query does.}

---

Just allow me to update a left join in such a manner that inserts a row as needed. -JH