![]() ![]() The FastAPI specific code is as small as always. Notice that most of the code is the standard SQLAlchemy code you would use with any framework. Sorry if I got some SQL wrong, I just threw this together.OAuth2 with Password (and hashing), Bearer with JWT tokensĬreate SQLAlchemy models from the Base classĬreate Pydantic models / schemas for reading / returning If you do know the owner types beforehand, I would go with a solution like Skerl. I would only suggest this if you don't know the owner types beforehand and they won't be linking to other tables. The downside is that you would have to look at the table to see what the owner types there are since it isn't immediately obvious based upon the schema. Owner_ID would not have a foreign key constraint but would be used as a reference to the other tables. With the above method, you could add as many Owner Types as you want. Owner_Type string NOT NULL, - In our example, this would be "User" or "Group" If you are not going to have a new Table for each new Owner type then maybe it would be good to include an owner_type instead of a foreign key column for each potential Owner: CREATE TABLE dbo.Group This solution is very similar to Skerl but looks different (up to preference). With this solution, you would continue to add new columns as you add new entities to the database and you would delete and recreate the foreign key constraint pattern shown by Skerl. If this will always be the case you can have something like this: CREATE TABLE dbo.Group In your example, each of the two existing owner types has their own table (which means you have something to reference). (The primary key on Ticket.ID is not necessary for this particular implementation, but it definitely wouldn't harm to have one in a table like this.)Īnother approach is to create an association table that contains columns for each potential resource type. (The respective columns in the other two tables are made primary keys accordingly.) The CK_Ticket_GroupUser check constraint ensures that only one of the two foreign key columns contains a reference (the other being NULL, that's why both have to be nullable). (ID),ĬASE WHEN OwnerGroup IS NULL THEN 0 ELSE 1 END +ĬASE WHEN OwnerUser IS NULL THEN 0 ELSE 1 END = 1Īs you can see, the Ticket table has two columns, OwnerGroup and OwnerUser, both of which are nullable foreign keys. (ID),ĬONSTRAINT FK_Ticket_User FOREIGN KEY REFERENCES dbo. ID int NOT NULL CONSTRAINT PK_Ticket PRIMARY KEY,ĬONSTRAINT FK_Ticket_Group FOREIGN KEY REFERENCES dbo. ID int NOT NULL CONSTRAINT PK_User PRIMARY KEY, ID int NOT NULL CONSTRAINT PK_Group PRIMARY KEY, Here's how it could look when applied to your tables: CREATE TABLE dbo. So, the referencing table had two foreign key columns, and also it had a constraint to guarantee that exactly one table (not both, not neither) was referenced by a single row. (One of them referenced two others, one at a time.) The first option in Skerl's list is what was implemented in a project I once worked with, where a similar relationship was established between three tables. int NOT NULL references dbo.Party(PartyId), PartyTypeId as cast(1 as tinyint) persisted,įoreign key (ID, PartyTypeId) references Party(PartyID, PartyTypeID) PartyTypeId as cast(2 as tinyint) persisted,įoreign key (ID, PartyTypeId) references Party(PartyId, PartyTypeID) PartyTypeId tinyint references dbo.PartyType(PartyTypeId), Heres a rough example using your posted schema: create table dbo.PartyType Or (my choice) model an entity that acts as a base for both Users and Groups, and have tickets owned by that entity. You could create a default group for every user and have tickets simply owned by either a true Group or a User's default Group. Perhaps in future you will want to allow a single ticket to be owned by multiple users or groups? This design does not enforce that a ticket must be owned by a single entity only. You could create M:M reference tables enabling both ticket:user and ticket:group relationships. ![]() ![]() You could simply create two columns in Ticket, OwnedByUserId and OwnedByGroupId, and have nullable Foreign Keys to each table. As always, the right design depends on your needs. ![]() You have a few options, all varying in "correctness" and ease of use. ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |