javaprogrammer javaprogrammer - 4 months ago 6
MySQL Question

How can I use foreign key for two tables to record expenses?

I want to use two tables, "employee_expenses" and "expense_master" to record and output all employee expenses. How can I do this using foreign keys?

employee_expense columns: Employee_ID(PK)

expense_master columns: Employee(FK to Employee_ID), expense_ID(PK) date, description, expense amount

There are four possible inputs for description column which are chosen from dropdown menu (accommodation, food, travel, other). Hence, the expense_ID(PK) in expense_master table will only have four values - "1,2,3,4"

I want to be able to output like this, with a list of all expenses incurred by a particular employee, for every employee to view from his account:

enter image description here

The input UI looks like this:
enter image description here

Please tell me what to do from here, i.e. what database action should be taken from here with regards to the two tables above, to make this happen, thanks.

Answer

Well you have asked very nicely repeatedly (including in chat with Sean). So I will share this. Sometimes we just need to accept that they guy doesn't know how to do it, and any pointers are working or understood. So ask away on the below.

drop table if exists employees;
create table employees
(   empId int auto_increment primary key,
    name varchar(100) not null
);

drop table if exists expenses;
create table expenses
(   expId int auto_increment primary key,
    category varchar(50) not null, -- maybe this goes to its own FK
    amount decimal(12,2) not null,
    empId int not null,
    constraint `fk_exp_emp001` foreign key (empId) references employees(empId),
    key `key_exps_empid` (empId)
);

Test:

insert expenses(category,amount,empId) values ('fishing supplies',100,10);
-- above line, Error 1452: FK violation. Emp 10 does not exist

insert employees(name) values ('Jim Smith'); -- id 1
select last_insert_id(); -- returns 1
insert expenses(category,amount,empId) values ('fishing supplies',100,1);
-- above inserts fine