Forum Discussion
Primary and Secondary tables, need help with relationship for auto create/update
Hi everyone! I'm getting into the weeds with a database i'm trying to update and could really use your help. Due to storage limits in the tables, i need to split the data and uploads into two tables. Both are classified by their respective "Case #" which i use as a primary key for both. I have been trying but can't seem to find a way for the primary table's Primary Key to auto populate into the secondary's Primary Key for each case. For example, if case # 114 is created in the primary table, i need case # 114 to auto create/populate into the secondary table so i can enter metrics/values in the primary table and upload files into the secondary table under the same case #. I've tried using the Relationships with Referential integrity and cascading updates/deletes between the two tables primary keys (the Case #), but that doesn't seem to work. Is this possible? If so, what am i doing wrong? And if it's impossible, what can i do to get the desired effect??
Thank you all for your time.
-Ray
Unfortunately, your description suggests an invalid, or inappropriate, table design. It sounds like you have what is often referred to as a "spreadsheet style" table, not a set of relational database tables.
Here's an excellent set of blog posts that explain the problem and offer ways to correct it.
I don't think it's a good idea, by the way, to offer you suggestions about working with this table design, even if it could be done without a wad of code.
- Thanks, I'll take a look at those threads and see what i can do! It's definitely a spreadsheet style table, mostly to have an easy data export to excel. There's simply too many values/columns for a single table, so that's why I was looking into a secondary extension.
Thanks again!
-RayRay_Barrit While it is true that tables in a normalized relational database application tend to have fewer columns in each table, that is a RESULT of the normalization process; it is not the goal in itself.
"There's simply too many values/columns for a single table" In other words, we have a principled process to reduce that number of columns. That process, called "normalization", is based on understanding RELATIONSHIPS between entities. When you do it that way, the natural outcome will be more tables with fewer fields. But, to repeat myself a bit, that's NOT the goal in and of itself.
