arrays in db?

[wooden stream]

Hi! How do you create an array in an SQL database? I come from a MongDB background so I'm a little lost about this right now. Help in the right direction is highly appreciated! Thank you!

[ivory frost]

so the thing about sql databases

is you don't create arrays

you would create a relationship

[ivory frost]

do you come from a java background?

[wooden stream]

No I don't come from a Java background, but more of JavaScript background.

[wooden stream]

Would you mind explaining a little more?

What I'm trying to do here is to have an array that kinda looks like this:

role1: [person1, person2], 
role2: [person3, person4]

I know I should create a person table, but I'm not sure how to connect person1 and 2 to role1 without the array.

[ivory frost]

do you want the array to be a fixed size at 2 or unlimited size

[wooden stream]

Unlimited size in this context. But what is difference in implementation between the two?

[ivory frost]

fixed size is a little more complex

do many people have many roles?

or do many people have 1 role?

[wooden stream]

People can have more than 1 role. And roles can have many people.

Many to many relationship

[ivory frost]

yes

i would make a many to many relationship

[wooden stream]

How would you make a many to many relationship in this context? Sorry to be a bother if I am... I know the concept, but I'm having a little trouble with the code at this point.

[ivory frost]

you would create three tables

Role

Person

and Role_Person

Role should have a role_id

Person should have a person_id

each record in Role should be a role

each record in Person should be a person

each record in Role_Person should hold a role_id and a person_id to relate which role a person may have, or in other terms, which person may have a role

this gives you maximum flexibility so that many people can have many roles

[wooden stream]

So if I have multiple types of roles, do I have to create even more tables in this case?

Looking at this:

role1: [person1, person2], 
role2: [person3, person4]

[ivory frost]

no

you should only have 3 tables max

think of a table like a class

a record in a table is like an object

[wooden stream]

On a greater scale, I have an entity that encompasses the roles.

Example:

Entity {
  name: ..., 
  type: ..., 
  role1: [person1, person2], 
  role2: [person3, person4]
}

Would the Person_Role table still apply in this context? Or must I create an Entity_Person_role table?

[ivory frost]

you should do something like this

Person {
  person_id: ...,
  name: ...,
  type: ...
}

Role {
  role_id: ...,
  name: ...
}

Person_Role {
  person_id: ...,
  role_id: ...
}

[wooden stream]

Yep I got this one.

So for Entity I don't have to worry about including the role1 and role2 in the table, but just do a join table while querying. Is that right?

[ivory frost]

what you told me is that people have many roles

does this many roles have to be 2 roles?

[wooden stream]

I tried to keep things simpler initially, but it looks like that backfired. Sorry.

Essentially, I have an Entity (which holds company information) table.

In this Entity table, there can be several roles, and each role can have several people.

[ivory frost]

oh

so many people have many roles

[wooden stream]

Yep

[ivory frost]

then yes

i would make 3 tables

[versed pawn]

If you want to include Entity as well, you would then have

  entity_id...,
  name: ...,
  type: ...,

  role_id...,
  name: ...,

  person_id...,
  name: ...,
  type: ...,

  entity_id: ...,
  role_id: ...
}```
```Person_Role {
  person_id: ...,
  role_id: ...
}```

That should cover everything in the Entity document in a SQL schema