merchant table
id
name
..
customer table
id
name
..
accounts table
id
merchant_id <-- has relation to merchant table (belongs)
customer_id <-- has relation to customer table (belongs)
When user is logged in as merchant and trying to add his acoount and at that time i don’t have customer_id , then i am getting error because of foreign key relation,
Error
CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`db`.`accounts`, CONSTRAINT `accounts_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`))...;
How to handle this scenario ?
if anyone has better idea then this schema design please share you idea.
itma
(Andrzej Bernat)
July 8, 2015, 8:25am
2
merchant table
id
name
..
customer table
id
name
..
accounts table
id
merchant_id <-- has relation to merchant table (belongs)
customer_id <-- has relation to customer table (belongs)
When user is logged in as merchant and trying to add his acoount and at that time i don’t have customer_id , then i am getting error because of foreign key relation,
Error
CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`db`.`accounts`, CONSTRAINT `accounts_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`))...;
How to handle this scenario ?
if anyone has better idea then this schema design please share you idea.
It depends on what you would like to achive. If you merchant may exist only with customer you have to have the both in DB. If merchant may exists independently just skip adding the relation to "accounts" and add it later, when customer exists.
merchant table
id
name
..
customer table
id
name
..
accounts table
id
merchant_id <-- has relation to merchant table (belongs)
customer_id <-- has relation to customer table (belongs)
When user is logged in as merchant and trying to add his acoount and at that time i don’t have customer_id , then i am getting error because of foreign key relation,
Error
CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`db`.`accounts`, CONSTRAINT `accounts_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`))...;
How to handle this scenario ?
if anyone has better idea then this schema design please share you idea.
In database Accounts table, set column customer_id as nullable (allow NULL value), and you will resolve the problem.
itma
(Andrzej Bernat)
July 8, 2015, 10:18am
4
I think it isn’t a good idea. What if he wants to have primary key from these two in the future?
But he already has a primary key defined on column id.
Here is table definition:
id -> Probably primary key
merchant_id <-- has relation to merchant table (belongs)
customer_id <-- has relation to customer table (belongs) ->foreign key allow NULL values
itma
(Andrzej Bernat)
July 8, 2015, 10:26am
6
But he already has a primary key defined on column id.
[b]
[/b]
Here is table definition:
id -> Probably primary key
merchant_id <-- has relation to merchant table (belongs)
customer_id <-- has relation to customer table (belongs) ->foreign key allow NULL values
It doesn’t indicate the future needs but right now your approach is the fastest one .
Yes, id is primary key.
merchant_id or customer_id , anyone must be blank.
I follow this table design because i want to keep all my accounts in one table and separate them by merchant_id and customer_id
@itma : I have already removed the relation and it is working
@duri : I will try with null values too
But When customer is logged in and try to add accounts then it will raise same error for merchant_id
How it will be if i change my database design as below,
accounts table
id
account_no
bank
...
merchant_accounts table
id
merchant_id
account_id
...
customer_accounts table
id
customer_id
account_id
...
if i follow new designs How should i list them as CGridview or unique validation (merchant wise unique account and customer wise unique accounts)?
Thanks for the input.
Yes, id is primary key.
merchant_id or customer_id , anyone must be blank.
I follow this table design because i want to keep all my accounts in one table and separate them by merchant_id and customer_id
@itma : I have already removed the relation and it is working
@duri : I will try with null values too
But When customer is logged in and try to add accounts then it will raise same error for merchant_id
How it will be if i change my database design as below,
accounts table
id
account_no
bank
...
merchant_accounts table
id
merchant_id
account_id
...
customer_accounts table
id
customer_id
account_id
...
if i follow new designs How should i list them as CGridview or unique validation (merchant wise unique account and customer wise unique accounts)?
Thanks for the input.
I will gladly help you, but can you write few words about your requirements so I can deeply understand problematic?