create database if not exists count;

use count;
drop table if exists users0;
drop table if exists users1;
drop table if exists urls2;
drop table if exists user2;
drop table if exists urls3;
drop table if exists user3;
drop table if exists company3;
drop table if exists url_rel4;
drop table if exists urls4;
drop table if exists user4;
drop table if exists company4;


create table users0 (
       name varchar(20) NOT NULL,
       company varchar(20) not null,
       company_address varchar(20) not null,
       url1 varchar(20) not null,
       url2 varchar(20) not null
);

insert into users0 (name, company, company_address, url1, url2) values ('Joe', 'ABC', '1 work Lane', 'abc.com', 'xyz.com');
insert into users0 (name, company, company_address, url1, url2) values ('Jill', 'XYZ', 'i Job Street', 'abc.com', 'xyz.com');
select * from users0;

create table users1 (
       name varchar(20) NOT NULL,
       company varchar(20) not null,
       company_address varchar(20) not null,
       url varchar(20) not null
);
insert into users1 (name, company, company_address, url) values ('Joe', 'ABC', '1 work Lane', 'abc.com');
insert into users1 (name, company, company_address, url) values ('Jill', 'XYZ', 'i Job Street', 'xyz.com');
insert into users1 (name, company, company_address, url) values ('Joe', 'ABC', '1 work Lane', 'xyz.com');
insert into users1 (name, company, company_address, url) values ('Jill', 'XYZ', 'i Job Street', 'abc.com');
select * from users1;


create table users2 (
    userid int AUTO_INCREMENT PRIMARY KEY,
    name varchar(20) NOT NULL,
    company varchar(20) not null,
    company_address varchar(20) not null
);
insert into users2 (name, company, company_address) values ('Joe', 'ABC', '1 work Lane');
insert into users2 (name, company, company_address) values ('Jill', 'XYZ', 'i Job Street');

create table urls2 (
    urlid int AUTO_INCREMENT PRIMARY KEY,
    relUserId int,
    url varchar(20),
    FOREIGN KEY(RelUserId) REFERENCES users2(userid)
);
insert into urls2 (reluserid, url) values (1, 'abc.com');
insert into urls2 (reluserid, url) values (1, 'xyz.com');
insert into urls2 (reluserid, url) values (2, 'abc.com');
insert into urls2 (reluserid, url) values (2, 'xyz.com');
select * from users2 join urls2 on urls2.reluserid=users2.userid;


create table company3 ( 
    companyID int AUTO_INCREMENT PRIMARY KEY,
    company varchar(20),
    company_address varchar(20)
);
insert into company3 (company, company_address) values ('ABC', '1 Work Lane');
insert into company3 (company, company_address) values ('XYZ', '1 Job Street');
create table user3 (
    userid int AUTO_INCREMENT PRIMARY KEY,
    name varchar(20) NOT NULL,
    RelCompanyId int,
    FOREIGN KEY(RelCompanyId) REFERENCES company3(companyID)
);
insert into user3 (name, RelCompanyId) values ('Joe', 1);
insert into user3 (name, RelCompanyId) values ('Jill', 2);
create table urls3 (
    urlid int AUTO_INCREMENT PRIMARY KEY,
    relUserId int,
    url varchar(20),
    FOREIGN KEY(RelUserId) REFERENCES users2(userid)
);
insert into urls3 (reluserid, url) values (1, 'abc.com');
insert into urls3 (reluserid, url) values (1, 'xyz.com');
insert into urls3 (reluserid, url) values (2, 'abc.com');
insert into urls3 (reluserid, url) values (2, 'xyz.com');
select * from user3 join urls3 on urls3.reluserid=user3.userid join company3 on user3.relcompanyid=company3.companyid;


create table company4 ( 
    companyID int AUTO_INCREMENT PRIMARY KEY,
    company varchar(20),
    company_address varchar(20)
);
insert into company4 (company, company_address) values ('ABC', '1 Work Lane');
insert into company4 (company, company_address) values ('XYZ', '1 Job Street');
create table user4 (
    userid int AUTO_INCREMENT PRIMARY KEY,
    name varchar(20) NOT NULL,
    RelCompanyId int,
    FOREIGN KEY(RelCompanyId) REFERENCES company4(companyID)
);
insert into user4 (name, RelCompanyId) values ('Joe', 1);
insert into user4 (name, RelCompanyId) values ('Jill', 2);
create table urls4 (
    urlid int AUTO_INCREMENT PRIMARY KEY,
    url varchar(20)
);
insert into urls4 (url) values ('abc.com');
insert into urls4 (url) values ('xyz.com');
create table url_rel4 ( 
    relationID int AUTO_INCREMENT PRIMARY KEY,
    RelURLid int,
    RelCompanyID int,
    FOREIGN KEY(RelURLid) REFERENCES urls4(urlid),
    FOREIGN KEY(RelCompanyId) REFERENCES company4(companyID)
);
insert into url_rel4 (RelURLid, relcompanyid) values (1, 1);
insert into url_rel4 (RelURLid, relcompanyid) values (1, 2);
insert into url_rel4 (RelURLid, relcompanyid) values (2, 1);
insert into url_rel4 (RelURLid, relcompanyid) values (2, 2);