0

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?

  • Why don't you store the IDs in the relation table instead of the name? You'd get the same effect, and that works "everywhere". – Mat Mar 26 '14 at 6:21
  • Is the question about Cassandra or a general one? – ypercubeᵀᴹ Mar 26 '14 at 10:42
  • For Cassandra, read this nice article: Cassandra Data Modeling Best Practices, Part 1, especially the "Example: Like relationship between User & Item" which is similar to your case. – ypercubeᵀᴹ Mar 26 '14 at 10:49
1
  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.

  • I accept (1) as my answer. But about (2), I don't plan to do JOIN(s). I've been studying about designing data model in Cassandra (which doesn't support JOIN) and still wondering about how to deal with its data duplication (de-normalization). How didn't I think about CASCADE! (facepalm) – hirikarate Mar 26 '14 at 8:05
  • I didn't meant JOIN specifically as an operation, but more as a concept to logically link two tables. In other words, I believe that the Customer_Group table should be defined as Customer_Group (CID, GID). – ddaniel Mar 26 '14 at 11:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.