My scenario is that a company has many branches and each branch has different employees. I have a user table and a branch table. User table contains a branch_id field which is its relationship to the branch table (branch id). I am using a dropdownlist populated from branch ids from the branch table. When a new employee account is created, the branch is chosen using this dropdownlist.
Some employee accounts need to be created in all the branches. If its a single or a combination of branches, i create the accounts individually. Can i have an option like All which if chosen can create the account in all branches? When i was doing this with php and mysql, i used checkbox list, and the sql query would run for all the checked branches (iterating over the returned array). How do i achieve such feature in yii?
Also, can i use wildcard (*) in relationship i.e. i choose * in branch_id and it would link the account to all the branches?
If you care about data integrity, branch_id must be EITHER existing in branches table id OR NULL (because it’s FK).
So the answer is: no, you should not.
Technically, you can use NULL value as a marker for "all" branches, but remember it is wrong, because NULL = "value unknown".
Consider changing your relation from "belongs to" to "many to many" using pivot table.
Otherwise some day you’ll face a situation when the user must be linked to some of the branches (not all of them), and it will be painful to modify existing code.
How should i design the database for the branch and employee? Shall i start with a branch and associate each branch with many employees? or my existing design starting with employee and linking each to a branch?
I have two tables, tbl_user and tbl_branch? Can i add subform to a form i.e. I start with branch (main form) and then employee (subform)?
