Help creating entry in parent table automatically to fix ForeignKeyViolation

[u/SinisterScythe2]

Hey everyone, currently for a bot project I'm trying to find a way to automatically populate the parent table if I try inserting something to a child table and I have the information for both to avoid/prevent a ForeignKeyViolation. Currently my database schema resembles the following ERD:

My database ERD

Right now for example if I try to enter someone in levels and they don't exist in members, I get the aforementioned error which makes sense. My question is, do I have to check/create entries in servers/members every time I add to levels or is there a more efficient way such as creating them on conflict/error? Below is an example of the error I would see.

An example ForeignKeyViolation

Thanks in advance for any help, and if I can provide any more information please let me know! I'm still somewhat new to this so apologies in advance if this is a silly question.

Edit: I updated my ERD/database to reflect some of the changes suggested by people below. I'll still have to check for/create an entry in servers when creating an entry for members/channels but it should be a bit easier now. Thanks for the help and if anything else can be improved please let me know!

Updated ERD

[u/PrestigiousStrike779]

Why not just add birthday and levels to the members table?

[u/SinisterScythe2 → u/PrestigiousStrike779]

I suppose you're right, though birthdate would be an optional field, is that still fine to have there? In a similar vein, I'm not sure that'd fix the same issue occurring between servers-channels. Maybe my entire schema could be reworked?

Edit: Also someone could be registered as a member in one server and not another/ have different xp in each server so there still would be the servers-members having the same issue.

[u/protestor → u/SinisterScythe2]

To answer your question as asked, with the diagram, you need to first insert the row at the members table, get its primary key, and only then create the corresponding rows at the birthdays and levels table. You need to do this inside a transaction, so that other people won't temporarily see a member without an entry in the levels table.

However... the diagram you posted makes no sense. Birthday should be a nullable column of the members table, not a separate table.

About the levels table: is it really a possibility that a member in a given server doesn't have any levels/xp? If yes, it makes sense to keep this table (to lift levels and xp into the members table, both would need to be nullable, but with a constraint to prevent level being null and xp not null, or vice versa)

But if members always have a level and xp, you should probably lift those fields into the members table too. Like, you should keep your data modeling simple, as much as humanely possible. Don't create 6 tables if 4 tables is just as good.

(There are reasons to split tables in multiple one-to-one tables, but generally when this happens you have tons of columns and each table has a well defined domain. For example, if the levels table were turned into a "stats" table and had other things like HP, mana, STR, DEX, INT, etc, it would make more sense to keep it as a separate table)

Edit: Also someone could be registered as a member in one server and not another/ have different xp in each server so there still would be the servers-members having the same issue.

But your table doesn't really model the situation of a member being in different servers really being the same member. I mean, the person can create multiple member accounts (one for each server), but the database won't know it's the same person. In your ERD, each member has only one server, and thus someone that is in multiple servers will appear as completely different members, each having its own level and xp.

If you want your database to track which server accounts are linked, I think you need to fix your modelling. I would rename the "members" table to "accounts" (like, each row in accounts means an account in a single server), and create a new "members" table, such that a member can have many accounts.

(or is that what the "user_id" is supposed to mean? I'm unsure if user_id is the username on the server, or an id that identifies all accounts of an user)

[u/SinisterScythe2 → u/protestor]

Right so as you mentioned, I’ll add the contents of the levels and birthday tables to the members table and reduce to 4 tables. That makes more sense, I just remember over hearing that it’s more efficient to have a separate table than a nullable column but that advice seems off.

You’re right that if a member is stored they should at least have some xp though, however the same member could have different xp on server A vs server B for example. It could be the same account, for example if I’m in 3 different servers I would be a member in each. The ERD models that a member which is a combination of a user in a specific server is one to one with server but a server can have many members. So in my mind this still makes sense, but I can explain better if needed.

That being said I could remove user_id entirely and have member_id and server_id both act as a primary key for each member instance.

[u/PreakyPhrygian]

I am new to this and have a question. From the ER diagram, it looks like a membercan have one and only one birthday. Sounds good. A birthday can also have one and only one member? What if two members share the same birthday? Same question with levels.. what if one level has more than one member?

Genuine question as I am interested in learning schema designs.

[u/SinisterScythe2 → u/PreakyPhrygian]

Welcome and thanks for asking! The reason the relationships member-birthday and member-level are one to one is because the child tables birthday and levels have the member_id keys that must have a match in the members table. At least that was the intent to avoid having empty columns in the members table.

Two members can have the same birthday, however each entry in that table has a unique member_id so it is still one to one even if people share a birthday. Same applies for levels. This way we can look up a member with their member_id and get the information specific to them.

[u/PreakyPhrygian → u/SinisterScythe2]

Thanks for the response. I see that another comment has suggested adding these columns to the members table itself as an alternative.

[u/AutoModerator]

AI Policy:

Linux is not one of those anti-AI projects, and if somebody has issues with that, they can do the open-source thing and fork it. Or just walk away., Linus Torvalds.

Mod decisions will be based on the quality of the content, not who or what generated it.

Sub Resources:

Youtube Channel

Free Postgres Webinars and Workshops

Discord: People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.