Scenario: I'm running tons of crawled images through a face-recognition api, the returned json has so many nodes that I had to divide all the fields into multiple tables if I enumerate the fields. e.g. following is one of the tables:
CREATE TABLE IF NOT EXISTS `face_landmark_info_nose` (
`face_id` VARCHAR(40) NOT NULL,
`pic_id` INT(11) NOT NULL,
`nose_contour_left1_x` DECIMAL(10, 6),
`nose_contour_left1_y` DECIMAL(10, 6),
`nose_contour_left2_x` DECIMAL(10, 6),
`nose_contour_left2_y` DECIMAL(10, 6),
`nose_contour_left3_x` DECIMAL(10, 6),
`nose_contour_left3_y` DECIMAL(10, 6),
`nose_contour_lower_middle_x` DECIMAL(10, 6),
`nose_contour_lower_middle_y` DECIMAL(10, 6),
`nose_contour_right1_x` DECIMAL(10, 6),
`nose_contour_right1_y` DECIMAL(10, 6),
`nose_contour_right2_x` DECIMAL(10, 6),
`nose_contour_right2_y` DECIMAL(10, 6),
`nose_contour_right3_x` DECIMAL(10, 6),
`nose_contour_right3_y` DECIMAL(10, 6),
`nose_left_x` DECIMAL(10, 6),
`nose_left_y` DECIMAL(10, 6),
`nose_right_x` DECIMAL(10, 6),
`nose_right_y` DECIMAL(10, 6),
`nose_tip_x` DECIMAL(10, 6),
`nose_tip_y` DECIMAL(10, 6),
PRIMARY KEY ('face_id, pic_id')
);
Later I noticed most fields are in a pattern of x value and y value for a single point, so I'm thinking maybe I could design a Point table like below:
CREATE TABLE IF NOT EXISTS `facepp_point` (
`face_id` VARCHAR(40) NOT NULL,
`pic_id` INT(11) NOT NULL,
`x_value` DECIMAL(10, 6),
`y_value` DECIMAL(10, 6),
`point_name` VARCHAR(40) NOT NULL,
PRIMARY KEY('face_id, pic_id')
);
Although by doing this, the program will be easy to maintain in the future, there are more or less 130 points like this in total for one face in one image and I would have to open one mysql query for each of them to present all the info on the website on the detail page, so should I enumerate all the fields or use the object-oriented fashion in designing the tables? Or is there a better way of doing this?
Generally whether you use attributes or a new table with depends on what kind of relation the 2 have to each other.
In your case I definitely say yes.
You have a 1 to many relation (1:n) it will be much better to keep one id for each point as attribute. Your point table would likely only exist out of an id and x and y value though. I don't see a reason to give it a name or add face_id or pic_id.
Now depending on whether a face always has the same amount of points (and the name matters) you would either add a point_id for each needed point as an attribute to the face table. Or if the order doesn't matter you give the point table a face_id and link it that way. (this way you don't have a bunch of attributes for no reason in the face table)
This might be a bit simplified, i don't know the exact workings of your use case. But in general you look at whether it is a 1:1, 1:n or n:n relationship and base your decision on that.