domcho
commented
4 years ago Added a table called "facilities" to represent locations/services.
CREATE TABLE facilities (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
ownerfk INT,
submitted_by VARCHAR( 80 ) NOT NULL ,
submitter_type VARCHAR( 20 ) NOT NULL ,
name VARCHAR( 160 ) NOT NULL ,
address VARCHAR( 180 ) NOT NULL ,
city VARCHAR( 20 ) NOT NULL ,
province_state VARCHAR( 20 ) NOT NULL ,
country VARCHAR( 20 ) NOT NULL ,
postal VARCHAR( 10 ) NOT NULL ,
email VARCHAR( 160 ) NOT NULL ,
phone VARCHAR( 16 ) NOT NULL ,
website VARCHAR( 512 ) NOT NULL ,
approved BOOLEAN DEFAULT false,
upvotes INT DEFAULT 0,
downvotes INT DEFAULT 0,
washroom BOOLEAN DEFAULT false,
shower BOOLEAN DEFAULT false,
reststop BOOLEAN DEFAULT false,
coffee BOOLEAN DEFAULT false,
snacks BOOLEAN DEFAULT false,
meal BOOLEAN DEFAULT false,
drivethrough BOOLEAN DEFAULT false,
walkthrough BOOLEAN DEFAULT false,
lat FLOAT( 10, 6 ) NOT NULL ,
lng FLOAT( 10, 6 ) NOT NULL,
INDEX(lat,lng,approved,upvotes,downvotes,washroom,shower,reststop,coffee,snacks,meal,drivethrough,walkthrough)
) ENGINE = MYISAM ;
iamjillsanluis
Create a SQL script with the database tables that will be used to store information about the users and the businesses / sites offering services.
Rough outline of tables:
Users: type (driver,business), username, salted password hash, email address, 2fa details, session info Business: owner foreign key (may be null), address, lat, long, list of services, hours of operation, photo, upvotes,downvotes
Use stored procedures for table insert/update operations.