Foreign key problems

[Antrim]

Hi. I'm programming an e-commerce/online store using servlets and a MySQL database, and I'm having some problems with the db right now. Hope somebody here smarter than me can give me a hand.

There are two kinds of users, both having their own table in the db: customers and admins. Both have an ID field, which is their PK.
Then I also have the table 'order', where I store the order ID (PK) and more info about the order, but also the ID of the person who purchased the order, which could be a customer or an admin. So, when I created the table 'order', I put two foreign keys: FK_id_admin (which relates to the field ID in the table 'admin') and FK_id_customer (relates to the field ID in the table 'customer'). Now, when I try to insert a new order purchased by a customer in the database, it won't let me, and will tell me this:

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`trigo_musical`.`order`, CONSTRAINT `FK_id_admin` FOREIGN KEY (`id`) REFERENCES `admin` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)

And if an admin is the one purchasing the order, it will show the same exception but with the other FK:

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`trigo_musical`.`order`, CONSTRAINT `FK_id_customer` FOREIGN KEY (`id`) REFERENCES `customer` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)

I realize that the problem is that I have two foreign keys in the same table referencing the same field in two different tables. So when I try to add an order purchased by a customer, it will look into the 'customer' table and see that the id is there, OK, but then IT ALSO looks in the 'admin' table and checks, and when it finds nothing that's where the excepcion appears. The problem is that I don't know how to fix this problem with the FK's. What should I change in my database?

Thanks in advance!

[urtrivedi]

First thing is that 2 FKs on same field is not a good idea and it will never work, I do not know how mysql allowed it.

Even if it work how you will know, when you look into your order, that who ordered this, customer or admin.

Also why you want to allow admin to order something?

1) One solution is use only customer table. then add one more column to customer table is_admin (y/n).
2) Now add admin rows to the customer table and set flag to Y in is_admin columns.
3) For other customer you may keep this flag to N.
4) Now remove that fk from admin table with order table. You may also drop admin table.

I think above steps may solve your problem without compromising your requirements.

[smantscheff]

With your design you have to allow NULL values in the foreign key fields so that an order can be related either to a customer or to an admin. But that would leave room for orphaned orders with neither customer nor admin associated with them. Also then an order could be assigned to both a customer and an admin which makes no sense (to me). Therefore I support urtridevis solution: get rid of the admin table. Admins are just users with some special rights and share most properties with customers. They belong in the same table.

[Antrim]

Thank you all for your answers. I've posted the same question and everybody seems to agree using only one table is the best solution.