0

I am from an optics company. Imagine that we have a flat rectangular glass plate (Length x width x thickness) which is a parent product that can be processed into smaller 2D shape child product such as circular glass plate, rectangular glass plate or triangle glass plate.

I would like to find a way to store size of that particular shape in a correct manner. For eg, a rectangular part is defined by lengthxwidthxthickness only but not by diameter. A circle is define by diameter and thickness but not by length and width. What is the correct way in keep track this kind of info?

For example, I can have a table which contain the following field:

  • ChildProductID
  • 2DShapeCategory = {circle, rectangle, triangle}
  • DiameterOfCircle
  • LengthOfRect
  • WidthOfRect
  • L1OfTriangle
  • L2OfTriangle
  • L3OfTriangle

And then for a circular part, the value of that row will be for eg:

productID=1 ,shape=circular , diamterofCircle=5 , lengthofRect=0, widthofRect=0, L1ofTriangle=0, L2OfTriangle=0, L3OfTriangle=0

then for a rect part, the value of that row can be for eg:

productID2, shape=rectangle, diameterofcircle=0, lenghtofRect=10, widthofRect=5, L1ofTriangle=0, L2OfTriangle=0, L3OfTriangle=0

But is this a correct way or best practice? I feel not and thus need some help and advice

Rominus
  • 1,181
  • 2
  • 14
  • 29
Chun Leong Lee
  • 11
  • 3
  • This seems like a vague question to me.Unless you are providing more information (such as: what is the objective of this database, is there actually common information between the squares, rectangles and circles which can be stored in a single column, what will be the use-case of the database (will there primarily be insertions or reads on it), etc.) any question will be opinion-based. – Erik A Aug 03 '17 at 13:45
  • Hi Erik sorry for that. I will edit the question – Chun Leong Lee Aug 04 '17 at 09:09
  • Read about 'database/SQL subtyping/polymorphism'. (Frequently wrongly described via implementation antipattern '(multiple'/many) FK(s) to multiple/many tables'.) – philipxy Aug 04 '17 at 12:08

1 Answers1

0

You're on the right track. Good practice would not include fields that aren't going to hold valid data. A better way is to use three tables; one for circles, one for triangles, and one for rectangles. Your parent product table would be productID and any fields that are about the parent product itself. Then you'd have these tables:

Circles_table:

productID child_circleID Diameter

Rectangle_table:

productID child_rectID length width

Triangles_table

productID child_triID L1 L2 L3

The productID field in each table being foreign key that links it to the parent product.

Rominus
  • 1,181
  • 2
  • 14
  • 29