Array Values in an RDBMS

user_14357151 0
Viewed 48

I'm trying to figure out the optimal way to create a database for a project. We're taking data from a bunch of projects and storing them into an RDBMS. One of the qualities of each project is the labels and users attached to each project.

Right now I have a table for users, a table for labels, and a table for projects that would ideally reference the PK IDs of the users and labels. I'm wondering which of these two options would be optimal for dealing with this data for use in an external resource like Tableau:

  1. We could theoretically store the ID's of each label and user in arrays in their respective columns to make the tables more concise and easy to understand.

  2. We have a separate column for each label and user and use bool values to show whether or not they're attributed.

Option 1 at first glance seems more attractive, but as we're using Db2 I'm not sure how feasible it is.

I understand there might not be enough information here. It's a pretty tight-lipped project so I can't give a ton of information away, but if you need more to offer advice, I can see what I can do. Thanks!

db2 rdbms sql
1 Answers

Neither. You would use junction/association tables:

project_labels
    project_label_id primary key
    project_id foreign key references projects(project_id)
    label_id foreign key references labels(label_id)

project_users
    project_user_id primary key
    project_id foreign key references projects(project_id)
    user_id foreign key references users(user_id)
user_11440350
Related