CODEWITHSUNDEEP
mysqldatabasedatabase-designdatabase-optimization
Dipendra Gurung
Dipendra Gurung

Reputation: 5870

Better way to reference foreign key in database

can you suggest the best practices for database optimization? Here is my database schema,

group(id, name, status)
users(id, group_id, parent_id, username, password)

company(id, name, address, phone)
agent(id, agent_code, name, address, phone)

In this schema i have four kinds of user group as super admin, company user, agent user and end user.

What is the best way to refer the users of each entities(company, agent)?

I have two options

A. Foreign key in users table as company_id and agent_id

users(id, group_id, parent_id, company_id, agent_id, username, password)

B. Foreign key as user_id in the company and agent table

company(id, user_id, name, address, phone)
agent(id, user_id, agent_code, name, address, phone)

I am using mysql as database and using cascade reference. When I delete a company/agent, associated users are automatically deleted as per option A. But in case of option B I must run second query to delete the associated users of a company/agent. So what do you suggest???

UPDATE: Forgot to mentioned about child users. A user of a company/agent can have sub users, referred by parent_id in the users table.

Upvotes: 0

Views: 91

Answers (1)

Ashish
Ashish

Reputation: 745

Option A sounds good.

Example :- as their can be suppose 100 users associated with just 5 companies and so can delete the single user in a single query...

Upvotes: 1

Related Questions