MySQL Database design for Taxi Service, need advise

Question

I am in the process of creating a database for a taxi booking service. I would like your help reviewing my design, also answering some questions i already have. First let me explain the logic. There will be no user log in. You will be required to give a Name,Last Name, email, phone, location,day and time, and your entry will be accepted.

Here are the tables.

CUSTOMER TABLE 

Customer_ID INT (PK,NN,AI,UN)
firstName VARCHAR(45) (NN)
lastName VARCHAR(45) (NN)
Email VARCHAR(50) (NN)
Phone VARCHAR(20) (NN)

RIDE

Ride_ID INT (PK,NN,AI,UN)
pickUpLocation TEXT(50) (NN)
Destination TEXT(50) (NN)
rideDate DATE (NN)
rideTime TIME (NN)
babyOnBoard VARCHAR(45)

My questions are as follows:

In MySQL Workbench, TIME has a parenthesis and needs a parameter, otherwise it is not accepted. What are the parameters? The same goes for TEXT. Are the TEXT parameters the same as VARCHAR?

2.What in your opinion should be the Foreign Key in Ride table? rideDate with rideTime?

If you have any other suggestions, will like to hear them.

This is my first database, so please take this into account.

Voting to reopen; the [_dup_](https://stackoverflow.com/questions/25300821/difference-between-varchar-and-text-in-mysql) is only partially relevant. — Rick James, Jan 27 '19 at 23:29
We cannot analyze a schema without knowing what queries will be applied to it. — Rick James, Jan 27 '19 at 23:30

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

TIME has a parenthesis and needs a parameter, otherwise it is not accepted. What are the parameters?

On MySQL v5.7, I was able to create a rideTime column without parentheses like this:

CREATE TABLE test (
  rideTime TIME
);

Are the TEXT parameters the same as VARCHAR?

No. From the documentation (my emphasis):

TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]

A TEXT column with a maximum length of 65,535 (216 − 1) characters. The effective maximum length is less if the value contains multibyte characters. Each TEXT value is stored using a 2-byte length prefix that indicates the number of bytes in the value.

An optional length M can be given for this type. If this is done, MySQL creates the column as the smallest TEXT type large enough to hold values M characters long.

TEXT types include LONGTEXT and TINYTEXT.

What in your opinion should be the Foreign Key in Ride table? rideDate with rideTime?

A foreign key refers to a row in a different table. So, assuming a 1:M relationship between Customers and Rides, a foreign key that would make since would be a CustomerID column in the RIDE table, like so:

FOREIGN KEY (CustomerID) REFERENCES CUSTOMER(Customer_ID)

rideDate and rideTime don't refer to another table, so they shouldn't be used as a foreign key.

I hope this answers all your questions!

MySQL Database design for Taxi Service, need advise

1 Answers1