Multiple Entries within an Attribute Cell

[wickjes]

What is the best way to manage data when you have multiple entries that need to go into any one attribute cell?

 

I'm doing curriculum mapping in Caspio. I’ve got my data tables set up and my relationships mapped out. However, I don’t quite understand what you would do when you have multiple entries for any attribute cell. For instance, maybe I have 3,000 lectures. For each lecture, I might have anywhere between 1 and 15 program objective tags I want to attach to it. I already have a table for my lectures and a table for my program objective tags. But, the column in my lecture table that references related program objective tag keys allows just one tag entry in each row's cell. One of my friends who’s worked lightly with databases told me you have to add multiple rows for each additional tag, so you might just put each lecture into your table 15 times so that you can tag it with each of the 15 program objectives you need to be attached to it or whatever. Maybe that truly is the only way, but it seems like it would create an unnecessary and astronomical workload involving duplicate data across the course of my 3,000 lectures...turning my data entry woes into more like 6,000-9,000+ lecture entries in my table. To me, it seems like that kind of defeats the purpose of using a database, but maybe I just don’t understand.

 

Any insight? I've watched the relationship videos and they don't clearly speak to this issue with a high volume of entries. Thanks!

[douvega]

Hi,

This is a many to many relationship case as I have understood. So, you give an ID to each of your lecture entries, then you have a third table to JOIN all this data together without restrictions. So in this new table, you only need a field for your Lecture_Rel_ID and other for the tag. If your tag is coming from another table, then just put the Tag_Rel_ID in this new table. 

I hope this can give you some guidance or light about further steps you might need to take to achieve what you are looking for.

Regards.

[SinJunYoung]

On 2/5/2021 at 6:10 AM, wickjes said:

What is the best way to manage data when you have multiple entries that need to go into any one attribute cell?

 

I'm doing curriculum mapping in Caspio. I’ve got my data tables set up and my relationships mapped out. However, I don’t quite understand what you would do when you have multiple entries for any attribute cell. For instance, maybe I have 3,000 lectures. For each lecture, I might have anywhere between 1 and 15 program objective tags I want to attach to it. I already have a table for my lectures and a table for my program objective tags. But, the column in my lecture table that references related program objective tag keys allows just one tag entry in each row's cell. One of my friends who’s worked lightly with databases told me you have to add multiple rows for each additional tag, so you might just put each lecture into your table 15 times so that you can tag it with each of the 15 program objectives you need to be attached to it or whatever. Maybe that truly is the only way, but it seems like it would create an unnecessary and astronomical workload involving duplicate data across the course of my 3,000 lectures...turning my data entry woes into more like 6,000-9,000+ lecture entries in my table. To me, it seems like that kind of defeats the purpose of using a database, but maybe I just don’t understand.

 

Any insight? I've watched the relationship videos and they don't clearly speak to this issue with a high volume of entries. Thanks!

Yup, this is definitely an example of a many to many relationships. Make sure to have a table lookup for your lectures and create an identifier, and create another table for another lookup which  is going to be records that are program objectives. In this way, you can create your third table to join these tables via two one-to-many relationships and finally creating a many-to-many relationship. Here you go Database Relationships (Part 3 of 3) - Many-to-Many Relationships - YouTube