Need help create relational database

Databases
1

Hi i need help how to fix a relational database in mysql.
i have this fields:

Company:
name
adress
country
adr_id

Employees:
name
email

i need to fix so its one to many.

also the problem is that the adr_id is the unique number for the company so if i inserts some data and the company adr_id already is in the database it should only update company details and insert a new employee if the employee is not in the database

hope you guys understand what i mean :slight_smile:

/Tony

2

So what code do you currently have. What you are trying to do is extremely simple so if your code isn’t working there is probably a logic error in it somewhere.

3

I have no code :slight_smile:
Only know what tables i need and what headers i need.

The problem i have is how to make the adr_id the PK and how to fix so the employees are attach to the right company by the adr_id

I have not done any relational databases before only ordinary databases.

So i need help to make the first step of create the database after that i would have to read about the quering to maintence the insert, update, delete, i figure that it would be different than only have one table.

4

I have tryed it like this dont know if im right on this


SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

CREATE TABLE IF NOT EXISTS `company` (
  `company_id` int(11) NOT NULL AUTO_INCREMENT,
  `company_name` varchar(30) NOT NULL,
  `company_address` varchar(50) NOT NULL,
  `company_zipcode` int(6) NOT NULL,
  `company_city` varchar(30) NOT NULL,
  `company_telephone` varchar(20) NOT NULL,
  `company_orgnr` int(10) NOT NULL,
  `company_main_focus` varchar(30) NOT NULL,
  `company_main_branch` varchar(50) NOT NULL,
  `company_esn` int(2) NOT NULL,
  `company_branchcode` varchar(100) NOT NULL,
  `company_employees` int(11) NOT NULL,
  `company_employees_text` varchar(30) NOT NULL,
  `company_turnover_text` varchar(30) NOT NULL,
  `company_turnover` int(15) NOT NULL,
  `company_county` varchar(50) NOT NULL,
  `company_province` varchar(50) NOT NULL,
  `company_addressid` int(9) NOT NULL,
  `company_country` varchar(2) NOT NULL,
  PRIMARY KEY (`company_id`),
  UNIQUE KEY `company_addressid` (`company_addressid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `person` (
  `person_id` int(11) NOT NULL AUTO_INCREMENT,
  `person_firstname` varchar(20) NOT NULL,
  `person_lastname` varchar(20) NOT NULL,
  `person_email` varchar(30) NOT NULL,
  `person_emailstatus` varchar(10) NOT NULL,
  `person_position` varchar(30) NOT NULL,
  `company_id` int(11) NOT NULL,
  PRIMARY KEY (`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
5

Hi!
this is my new code



SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


CREATE TABLE IF NOT EXISTS `company` (
  `company_id` int(11) NOT NULL AUTO_INCREMENT,
  `company_name` varchar(30) NOT NULL,
  `company_address` varchar(50) NOT NULL,
  `company_zipcode` int(6) NOT NULL,
  `company_city` varchar(30) NOT NULL,
  `company_telephone` varchar(20) NOT NULL,
  `company_orgnr` int(10) NOT NULL,
  `company_main_focus` varchar(30) NOT NULL,
  `company_main_branch` varchar(50) NOT NULL,
  `company_esn` int(2) NOT NULL,
  `company_branchcode` varchar(100) NOT NULL,
  `company_employees` int(11) NOT NULL,
  `company_employees_text` varchar(30) NOT NULL,
  `company_turnover_text` varchar(30) NOT NULL,
  `company_turnover` int(15) NOT NULL,
  `company_county` varchar(50) NOT NULL,
  `company_province` varchar(50) NOT NULL,
  `company_addressid` int(9) NOT NULL,
  `company_country` varchar(2) NOT NULL,
  PRIMARY KEY (`company_id`),
  UNIQUE KEY `company_addressid` (`company_addressid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `person` (
  `person_id` int(11) NOT NULL AUTO_INCREMENT,
  `person_firstname` varchar(20) NOT NULL,
  `person_lastname` varchar(20) NOT NULL,
  `person_email` varchar(30) NOT NULL,
  `person_emailstatus` varchar(12) NOT NULL,
  `person_position` varchar(50) NOT NULL,
  `company_addressid` int(9) NOT NULL,
  PRIMARY KEY (`person_id`),
  KEY `FK_company_addressid` (`company_addressid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

ALTER TABLE `person`
  ADD CONSTRAINT `FK_company_addressid` FOREIGN KEY (`company_addressid`) REFERENCES `company` (`company_addressid`) ON DELETE CASCADE ON UPDATE CASCADE;

But my next problem is that i have the info in csv files how do i insert that so it also fill the both tables?