Two foreign key in one table

Yii 1.1 General Discussion
1

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.

2

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.

3

In database Accounts table, set column customer_id as nullable (allow NULL value), and you will resolve the problem.

4

I think it isn’t a good idea. What if he wants to have primary key from these two in the future?

5

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

6

It doesn’t indicate the future needs but right now your approach is the fastest one :).

7

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.

8

I will gladly help you, but can you write few words about your requirements so I can deeply understand problematic?