Ovako imam jedan pa sada verovatno problem, radim neki softwer ali sam posustao u vezi baze. Napravio sam jednu testnu bazu i njoj izmedju ostalih tabela imam tabele: Population, CustomerEmployee, Employee.
Moj problem je sledeci, tabela "Employee" sadrzi zaposlene koji se loguju u program, isto tako tabela "CustomerEmployee" sadrzi ko se loguje u program ali sa klijentske strane. Meni treba da u tabeli "Population" dobijem ko je napravio zapis, datum kreiranja, ko je zadnji izmenuio i datum izmene stim sto to moze biti i Employee ali moze i CustomerEmployee.
Evo i koda od tabela, ako bude trebalo sta da pojasnim slobodno recite i hvala unapred!!!!!
Code (sql):
/*
Navicat MySQL Data Transfer
Source Server : localhost
Source Server Version : 100113
Source Host : localhost:3306
Source Database : test
Target Server Type : MYSQL
Target Server Version : 100113
File Encoding : 65001
Date: 2016-10-27 22:39:28
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for customer
-- ----------------------------
DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer` (
`UniqueID` INT(11) NOT NULL AUTO_INCREMENT,
`Name` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`UniqueID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of customer
-- ----------------------------
INSERT INTO `customer` VALUES ('1', 'Radnja 1');
INSERT INTO `customer` VALUES ('2', 'Radnja 2');
-- ----------------------------
-- Table structure for customeremployee
-- ----------------------------
DROP TABLE IF EXISTS `customeremployee`;
CREATE TABLE `customeremployee` (
`UniqueID` INT(11) NOT NULL AUTO_INCREMENT,
`CustomerID` INT(11) DEFAULT NULL,
`FirstName` VARCHAR(255) DEFAULT NULL,
`LastName` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`UniqueID`),
KEY `ce_customer_fk` (`CustomerID`),
CONSTRAINT `ce_customer_fk` FOREIGN KEY (`CustomerID`) REFERENCES `customer` (`UniqueID`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of customeremployee
-- ----------------------------
INSERT INTO `customeremployee` VALUES ('1', '1', 'Marko', 'Markovic');
INSERT INTO `customeremployee` VALUES ('2', '1', 'Djuro', 'Djuric');
INSERT INTO `customeremployee` VALUES ('6', '2', 'Milo', 'Milic');
-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
`UniqueID` INT(11) NOT NULL AUTO_INCREMENT,
`FirstName` VARCHAR(255) DEFAULT NULL,
`LastName` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`UniqueID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of employee
-- ----------------------------
INSERT INTO `employee` VALUES ('1', 'Petar', 'Petrovic');
INSERT INTO `employee` VALUES ('2', 'Ostoja', 'Ostojic');
-- ----------------------------
-- Table structure for population
-- ----------------------------
DROP TABLE IF EXISTS `population`;
CREATE TABLE `population` (
`UniqueID` INT(11) NOT NULL AUTO_INCREMENT,
`SerialNumber` BIGINT(20) DEFAULT NULL,
`ProductID` INT(11) DEFAULT NULL,
`Status` INT(11) DEFAULT NULL,
`Note` longtext,
PRIMARY KEY (`UniqueID`),
KEY `p_product_fk` (`ProductID`),
CONSTRAINT `p_product_fk` FOREIGN KEY (`ProductID`) REFERENCES `product` (`UniqueID`) ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of population
-- ----------------------------
INSERT INTO `population` VALUES ('1', '1111', '2', '1', NULL);
INSERT INTO `population` VALUES ('2', '2222', '1', '2', NULL);
-- ----------------------------
-- Table structure for populationbuyer
-- ----------------------------
DROP TABLE IF EXISTS `populationbuyer`;
CREATE TABLE `populationbuyer` (
`PopulationID` INT(11) NOT NULL,
`CustomerID` INT(11) DEFAULT NULL,
KEY `PopulationID` (`PopulationID`),
KEY `pb_customer_fk` (`CustomerID`) USING BTREE,
CONSTRAINT `populationbuyer_ibfk_1` FOREIGN KEY (`CustomerID`) REFERENCES `customer` (`UniqueID`) ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT `populationbuyer_ibfk_2` FOREIGN KEY (`PopulationID`) REFERENCES `population` (`UniqueID`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;
-- ----------------------------
-- Records of populationbuyer
-- ----------------------------
INSERT INTO `populationbuyer` VALUES ('1', '2');
-- ----------------------------
-- Table structure for populationenduser
-- ----------------------------
DROP TABLE IF EXISTS `populationenduser`;
CREATE TABLE `populationenduser` (
`PopulationID` INT(11) NOT NULL,
`CustomerID` INT(11) DEFAULT NULL,
KEY `PopulationID` (`PopulationID`),
KEY `pb_customer_fk` (`CustomerID`) USING BTREE,
CONSTRAINT `populationenduser_ibfk_1` FOREIGN KEY (`CustomerID`) REFERENCES `customer` (`UniqueID`) ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT `populationenduser_ibfk_2` FOREIGN KEY (`PopulationID`) REFERENCES `population` (`UniqueID`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;
-- ----------------------------
-- Records of populationenduser
-- ----------------------------
INSERT INTO `populationenduser` VALUES ('1', '1');
-- ----------------------------
-- Table structure for product
-- ----------------------------
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
`UniqueID` INT(11) NOT NULL AUTO_INCREMENT,
`Description` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`UniqueID`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of product
-- ----------------------------
INSERT INTO `product` VALUES ('1', 'Voda');
INSERT INTO `product` VALUES ('2', 'Hleb');
INSERT INTO `product` VALUES ('3', 'Kasika');
INSERT INTO `product` VALUES ('4', 'Viljuska');
INSERT INTO `product` VALUES ('5', 'Torba');
INSERT INTO `product` VALUES ('6', 'Motor');
SET FOREIGN_KEY_CHECKS=1;
/*
Navicat MySQL Data Transfer
Source Server : localhost
Source Server Version : 100113
Source Host : localhost:3306
Source Database : test
Target Server Type : MYSQL
Target Server Version : 100113
File Encoding : 65001
Date: 2016-10-27 22:39:28
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for customer
-- ----------------------------
DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer` (
`UniqueID` INT(11) NOT NULL AUTO_INCREMENT,
`Name` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`UniqueID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of customer
-- ----------------------------
INSERT INTO `customer` VALUES ('1', 'Radnja 1');
INSERT INTO `customer` VALUES ('2', 'Radnja 2');
-- ----------------------------
-- Table structure for customeremployee
-- ----------------------------
DROP TABLE IF EXISTS `customeremployee`;
CREATE TABLE `customeremployee` (
`UniqueID` INT(11) NOT NULL AUTO_INCREMENT,
`CustomerID` INT(11) DEFAULT NULL,
`FirstName` VARCHAR(255) DEFAULT NULL,
`LastName` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`UniqueID`),
KEY `ce_customer_fk` (`CustomerID`),
CONSTRAINT `ce_customer_fk` FOREIGN KEY (`CustomerID`) REFERENCES `customer` (`UniqueID`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of customeremployee
-- ----------------------------
INSERT INTO `customeremployee` VALUES ('1', '1', 'Marko', 'Markovic');
INSERT INTO `customeremployee` VALUES ('2', '1', 'Djuro', 'Djuric');
INSERT INTO `customeremployee` VALUES ('6', '2', 'Milo', 'Milic');
-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
`UniqueID` INT(11) NOT NULL AUTO_INCREMENT,
`FirstName` VARCHAR(255) DEFAULT NULL,
`LastName` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`UniqueID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of employee
-- ----------------------------
INSERT INTO `employee` VALUES ('1', 'Petar', 'Petrovic');
INSERT INTO `employee` VALUES ('2', 'Ostoja', 'Ostojic');
-- ----------------------------
-- Table structure for population
-- ----------------------------
DROP TABLE IF EXISTS `population`;
CREATE TABLE `population` (
`UniqueID` INT(11) NOT NULL AUTO_INCREMENT,
`SerialNumber` BIGINT(20) DEFAULT NULL,
`ProductID` INT(11) DEFAULT NULL,
`Status` INT(11) DEFAULT NULL,
`Note` longtext,
PRIMARY KEY (`UniqueID`),
KEY `p_product_fk` (`ProductID`),
CONSTRAINT `p_product_fk` FOREIGN KEY (`ProductID`) REFERENCES `product` (`UniqueID`) ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of population
-- ----------------------------
INSERT INTO `population` VALUES ('1', '1111', '2', '1', NULL);
INSERT INTO `population` VALUES ('2', '2222', '1', '2', NULL);
-- ----------------------------
-- Table structure for populationbuyer
-- ----------------------------
DROP TABLE IF EXISTS `populationbuyer`;
CREATE TABLE `populationbuyer` (
`PopulationID` INT(11) NOT NULL,
`CustomerID` INT(11) DEFAULT NULL,
KEY `PopulationID` (`PopulationID`),
KEY `pb_customer_fk` (`CustomerID`) USING BTREE,
CONSTRAINT `populationbuyer_ibfk_1` FOREIGN KEY (`CustomerID`) REFERENCES `customer` (`UniqueID`) ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT `populationbuyer_ibfk_2` FOREIGN KEY (`PopulationID`) REFERENCES `population` (`UniqueID`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;
-- ----------------------------
-- Records of populationbuyer
-- ----------------------------
INSERT INTO `populationbuyer` VALUES ('1', '2');
-- ----------------------------
-- Table structure for populationenduser
-- ----------------------------
DROP TABLE IF EXISTS `populationenduser`;
CREATE TABLE `populationenduser` (
`PopulationID` INT(11) NOT NULL,
`CustomerID` INT(11) DEFAULT NULL,
KEY `PopulationID` (`PopulationID`),
KEY `pb_customer_fk` (`CustomerID`) USING BTREE,
CONSTRAINT `populationenduser_ibfk_1` FOREIGN KEY (`CustomerID`) REFERENCES `customer` (`UniqueID`) ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT `populationenduser_ibfk_2` FOREIGN KEY (`PopulationID`) REFERENCES `population` (`UniqueID`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;
-- ----------------------------
-- Records of populationenduser
-- ----------------------------
INSERT INTO `populationenduser` VALUES ('1', '1');
-- ----------------------------
-- Table structure for product
-- ----------------------------
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
`UniqueID` INT(11) NOT NULL AUTO_INCREMENT,
`Description` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`UniqueID`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of product
-- ----------------------------
INSERT INTO `product` VALUES ('1', 'Voda');
INSERT INTO `product` VALUES ('2', 'Hleb');
INSERT INTO `product` VALUES ('3', 'Kasika');
INSERT INTO `product` VALUES ('4', 'Viljuska');
INSERT INTO `product` VALUES ('5', 'Torba');
INSERT INTO `product` VALUES ('6', 'Motor');
SET FOREIGN_KEY_CHECKS=1;