Database engine that share a row among multiple tables

database-designdatabase-enginevirtual-columns

I'm wondering whether exists any database engine that can share cells or rows amongst many tables. For example:

TABLE Customer (
   CID: int
   Fullname: nvarchar(50)
   Age: int
)

TABLE Group (
   GID: int
   Name: nvarchar(50)
)

TABLE Customer_Group (
   CusName: nvarchar(50)
   GrpName: nvarchar(50)
)

In which, Customer_Group is designated for a certain query, like a column family in Cassandra. Now I want to insert to Customer_Group table a row whose CusName cell references to Fullname cell of a row in table Customer. Similar to GrpName.

As a result, when I update Fullname in Customer table, I don't have to update Customer_Group table, because it references to same cell address. And Customer_Group table itself doesn't have to store actual data for CusName but in fact is a pointer.

Does anyone know what database engine supports this idea? And is it a good idea though?

Best Answer

  1. Isn't this what CASCADE option on foreign key is supposed to do? You can find that in many DB engines.
  2. It is not ok to perform the join on Fullname column as long as you have a CID column, which is supposed to be the identity. It is just a bad practice, and can have undesired effects - like the one you encountered.

In conclusion, either you change the joining column, or try to active the cascading on update capabilities of your DBMS.

Related Question