0

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.

Dipendra Gurung
  • 5,720
  • 11
  • 39
  • 62
  • Can the same user belong to more than company? What about agent? – Branko Dimitrijevic Oct 25 '13 at 08:15
  • @BrankoDimitrijevic No, a user can not belongs to more than a company/agents. – Dipendra Gurung Oct 25 '13 at 08:27
  • Then you need option A. What exactly is the problem with deleting the company? You don't necessarily need to delete company's users when deleting the company - you can just set `users.company_id` to NULL. – Branko Dimitrijevic Oct 25 '13 at 08:30
  • @BrankoDimitrijevic thanks for your response :), ya i want to delete users associated with a company, but also making sure that the database is optimized. But If I have lots of end users then, will these two fields company_id and agent_id set to NULL affect the size of database? I mean the fields will be less usable. Do you have any other option then this? Thanks! in advance... :) – Dipendra Gurung Oct 25 '13 at 08:51
  • If you want the user deleted, then you don't need to (and indeed can't, since the row no longer exists) set the FK field to NULL. You can automate the deletion by using [ON DELETE CASCADE](http://stackoverflow.com/a/12186780/533120). "Optimized" can mean a lot of things - please be more specific. – Branko Dimitrijevic Oct 25 '13 at 08:57
  • Ya, I have already done that cascade deletion. By saying end user, I meant the "public users" of the site which does not belongs to any company/agent. So if I have lots of public users, then these two fields company_id and agent_id becomes meaningless. So if I go for this option, does setting NULL to company_id and agent_id for every public user a good practice? Do we have another alternative structure to overcome this problem? – Dipendra Gurung Oct 25 '13 at 09:25
  • This is not a problem. – Branko Dimitrijevic Oct 25 '13 at 10:18

1 Answers1

1

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...

Ashish
  • 735
  • 1
  • 6
  • 15