What should be the foreign key that the client sends when creating a new record in a database?-CodePudding

I am making a web application with python, fastapi and postgresql. Within the database there are two related entities: "departments" and "users", with a one-to-many relationship:

These are pydantic models:

# Shared properties
class UserBase(BaseModel):
    username: str | None = None
    email: EmailStr | None = None
    role: Roles | None = None

# Properties to receive via API on creation
class UserCreate(UserBase):
    username: str
    password: str
    role: Roles
    department_id: int # ???

Therefore, when a user is created in the application, a foreign key must be sent that points to the department to which the user belongs. By convention, this foreign key is the id(Pk) of the department, however I thought that perhaps when the client wants to create a new user, it will be difficult for him to remember the id of the department to which he wants to associate it. How should I handle it?

CodePudding user response：

One solution is to allow the client to specify the department name instead of the id, and then use the name to look up the department id in the database before creating the user.

Here's how you could modify the UserCreate model to accept the department name instead of the id:

class UserCreate(UserBase):
    username: str
    password: str
    role: Roles
    department_name: str # instead of department_id

In your FastAPI endpoint for creating users, you can first look up the department id using the name, then create the user with the id:

@app.post("/users/", response_model=User)
async def create_user(user: UserCreate):
    department = await db.fetch_one(
        """
        SELECT * FROM departments WHERE name = $1
        """,
        user.department_name,
    )
    if not department:
        raise HTTPException(
            status_code=400, detail=f"Department {user.department_name} not found"
        )
    department_id = department["id"]

    # create user with department_id
    ...

CodePudding user response：

Are you by chance planning to use an ORM in your web app, something like SQL Alchemy?

If so, you may be able to model your solution from their examples. The (many) item to User relationship in this example may also be a good guide. I'm less familiar with your particular stack than some others but hopefully this can be of a small help.

I'm constantly handed API-based integrations where I don't think the original creators actually considered the dependent needs of their designs, on behalf of your future API consumers - thank you for putting thought into this up front :)

Just brainstorming and in generality it is reasonable in API design when an attribute is required but is based on a look-up value from another table so long as you have get endpoints to allow your API Consumers to query those dependent entities. You'd typically present your Departments in a some sort selection-list, anyway.

I've also seen developers lean on tags or codes (sort of like the PLU codes on fruit and veggies) for fast-Create, heavily repetitive UIs where users/staff are trained to enter values quickly and can enter the related field (in your case, department) but those were pretty specialized in terms of business requirements: like retail inventory counters.