Varieur: JFlash: the danger with generic relation tables is that you have to store the fk of each element in separate columns, and the type of relation, and a flag to invalidate it.
Lowney: So when I query for “likes” I also make sure active=true, otherwise it means that relationship has been invalidated. what do you think?
Braune: Yes 4 columns. what is wrong with that?
Varieur: JFlash: and depending on the type of relation, you have to join a specific table
Fleites: And btw, what is the list of doom?
Varieur: Your code will need to handle that case
Varieur: JFlash: check the link from the bot.
Oard: A better approach might be to have an object table that represents all objects. The type of each object could be user, group, event, etc. The relationship mapping would be object to object. No need for 4 different columns to hold the foreign keys.
Mogush: Well adding extra columns to aco**** for node types should not be problem, it’s probably a good idea, btw.
Legg: I will need foreign keys anyway to get info about those nodes because i will need therir names, profile pictures and so fourth
Hilu: JFlash: No. You’re not thinking straight.
Sanmartin: So the foreign keys are unavoidable, imho
Toelkes: Xgc, no problem mate. please be patient with me . I1m still learning :
Sepeda: JFlash: One row per relationship connection in the mappnig table. not one column per connection.
Loran: I did not say one collum per connection
Wallentine: JFlash: show the schema you are proposing.
Pata: Columns are : from , to, rel_type, active
Slosser: JFlash: What is rel_type? That seems unnecessary.
Muster: And now that you suggested from_type and to_type columns sound great
Beiley: JFlash: The type is ***ociated with the object, not the connection.
Telecky: Don’t copy that detail.
Berenschot: It’s the relationship descriptor: owns, likes, attends, rsvp_yes, blocked
Almaraz: Oh. That’s different.
Rodriuez: Sure. You could do that.
Praytor: Btw, I will also throw in a count column, which makes sense for a few relationships
Denooyer: I don’t think you need a from_type / to_type. The relationship is ***umed to be from – to
Sheriff: Yes but then for some operations I’d have to make some joined queries to know what type those from and to are, no?
Pangle: Each object has a type.
Freilich: For isntance say I want to know. how many events joe attended in 2015
Mcdowell: JFlash: yes. Joins are fundamental. Don’t avoid them by denormalization unless you have a very very good reason.
Chase: If I have a from_type , I do not need to use a join
Krom: JFlash: Don’t do that.
Villaquiran: Mind you, from_type never changes because a user cannot become a group and the other way around
Overhulser: JFlash: That’s something you could add later, if you have a need. It’s unlikely you have a need. It’s just bad to copy data without a good reason.
Orendorff: Xgc, the reason is performance. this has to be designed to run fast on several thousand requests per second
Wieckowski: But that data will not change, ever
Losada: UUID xyz will always belong to a node of type ‘event’ or ‘group’ or ‘user’
Frabizzio: JFlash: Then you think you have a need. Go for it.
Corbridge: Ok, thanks for the feedback
Dicorcia: Does anyone know how can I index this table?
Elman: I mean, should I have a separete primary key or something. I don’t know much about this
Matelic: JFlash: Depends on the query.
Fiorentino: JFlash: You need a primary key on each table.
Vigorito: Like a auto increasing pk?
Woodward: JFlash: No. That’s up to you.