JacHSq82

1CREATE TABLE IF NOT EXISTS `company` (
2  id UNSIGNED INT NOT NULL auto_increment,
3  `SIRET` varchar(50) NOT NULL,
4  `nom` varchar(50) NOT NULL,
5  `description` varchar(500) NOT NULL,
6  `enable` ENUM('YES', 'NO') DEFAULT 'YES',
7  `level` int(1) NOT NULL,
8  PRIMARY KEY  (id),
9  UNIQUE KEY SIRET (SIRET)
10) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
11
12// contactType can be one of the 3 values : email, phone, fax
13CREATE TABLE IF NOT EXISTS `contactType` (
14  id UNSIGNED INT NOT NULL auto_increment,
15  `contactType` ENUM('email', 'phonenumber', 'faxnumber')
16  `mobile` ENUM('YES', 'NO') default 'NO',
17  PRIMARY KEY  (id),
18  UNIQUE KEY type (contactType)
19) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
20
21CREATE TABLE IF NOT EXISTS `contacts` (
22  id UNSIGNED INT NOT NULL auto_increment,
23  `SIRET` varchar(50) NOT NULL,
24  `contactType` varchar(50) NOT NULL, // A reference to contactType just above
25  `contactref` varchar(50) NOT NULL, // Phone number, fax number or email adress
26  PRIMARY KEY  (id),
27  UNIQUE KEY type (SIRET)
28) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
29
30CREATE TABLE IF NOT EXISTS `customers` (
31  id UNSIGNED INT NOT NULL auto_increment,
32  `SIRET` varchar(50) NOT NULL,
33  PRIMARY KEY  (id),
34  UNIQUE KEY type (SIRET)
35) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
36
37CREATE TABLE IF NOT EXISTS `supplier` (
38  id UNSIGNED INT NOT NULL auto_increment,
39  `SIRET` varchar(50) NOT NULL,
40  PRIMARY KEY  (id),
41  UNIQUE KEY type (SIRET)
42) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
43
44CREATE TABLE IF NOT EXISTS `industry` (
45  id UNSIGNED INT NOT NULL auto_increment,
46  `industry` varchar(250) NOT NULL,
47  PRIMARY KEY  (id),
48  UNIQUE KEY type (activite)
49) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
50
51CREATE TABLE IF NOT EXISTS `entreprisePerIndustry` (
52  id UNSIGNED INT NOT NULL auto_increment,
53  `industry_id` varchar(250) NOT NULL, // Chemical, Computer, Consulting, ...
54  FOREIGN KEY (industry_id) REFERENCES industry(id) ON DELETE CASCADE,
55  `SIRET` varchar(50) NOT NULL,
56  PRIMARY KEY  (id),
57  UNIQUE KEY type (industry_id)
58) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
59	
60`id` INT UNSIGNED NOT NULL AUTO_INCREMENT`
61	
62`enable` ENUM( 'y', 'n' ) NOT NULL DEFAULT 'y' COMMENT 'y:yes; n:no' `
63	
64--
65-- Table structure for table `tbl_company`
66--
67CREATE TABLE IF NOT EXISTS `tbl_company` (
68  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
69  `siret` varchar(50) NOT NULL,
70  `nom` int(11) NOT NULL,
71  `description` varchar(250) NOT NULL,
72  `enable` enum('y','n') NOT NULL DEFAULT 'y' COMMENT 'y:yes; n:no',
73  `level` enum('1','2') NOT NULL DEFAULT '1',
74  `last_updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
75  PRIMARY KEY (`id`)
76) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
77
78--
79-- Table structure for table `tbl_contact`
80--
81
82CREATE TABLE IF NOT EXISTS `tbl_contact` (
83  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
84  `siret` varchar(50) NOT NULL,
85  `contact_type` enum('email','phone','fax') NOT NULL DEFAULT 'email',
86  `contact_ref` varchar(100) NOT NULL COMMENT 'Phone number, fax number or email adress',
87  `last_updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
88  PRIMARY KEY (`id`),
89  UNIQUE KEY `siret` (`siret`)
90) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
91
92...
93...