One-to-zero-or-one relationship

dharmatech

Hey y'all,

Just posted this question to stackoverflow regarding modeling a one-to-zero-or-one relationship.

Any suggestions (here or there) are welcome.

Hopefully the answers can serve as a reference for folks needing to setup this sort of thing in the future.

dharmatech

Updated the post with a couple more things I've tried.

I updated the schema as follows:

CREATE TABLE office_assignments (
    instructor_id UUID PRIMARY KEY NOT NULL,
    "location" TEXT NOT NULL
);

However, the generated Types.hs doesn't compile.

dharmatech

Zooming out a bit, I'm just wondering, is there some feature that would need to be added to IHP to support this kind of schema? E.g. supporting a table without an id PRIMARY KEY column but with another column instead which is both a PRIMARY KEY as well as being a foreign key.

If so, is that a feature that's already on the roadmap? Let me know if I should create a github issue.

I'm in no rush or anything; just getting to know what IHP is currently able to easily handle. I'll be patient if this isn't yet a straightforward thing to implement. :-)

dharmatech

And if you think I should take an entirely different approach than the one outlined there (based on the ASP.NET Core tutorial) I'm open to that kind of suggestion as well. :-)

marc PRO

Right now IHP assumes that all records have an id column, which is the primary key. Many internal functions use this for having a simpler implementation.

There's already a github issue for this (and also for improving the situation around composite primary keys (PKs of two columns or more))

dharmatech

@marc wrote:

Right now IHP assumes that all records have an id column, 
which is the primary key. Many internal functions use 
this for having a simpler implementation.

There's already a github issue for this (and also for
improving the situation around composite primary keys 
(PKs of two columns or more))

OK, sounds good. I'll be patient and wait for those to be implemented before pushing further with a schema like I'm dealing with here. :-)

Regarding the current limitation that all tables must have an id column, I found the following seemingly related issue:

build/Generated/Types.hs "Not in scope" error when creating a table without an id primary key #696

Is that the one you're referring to, Marc?

I'd like to subscribe/follow the requisite issues so that I know when to give things a whirl again. Seems like it would be a good test case for the issue.