What's the best way to store object's dimension in a Database?

I'm developing a database and need to know the best way to store dimensions in a Database. Some objects have Length, width, height & depth. Some have length and width only. How to store these data proper开发者_开发问答ly in the database?

---

You relate the object with the Dimension type if you want it normalized.

So if I had an object that had length and width only

Object1 Width  10ft
Object1 Length 20ft

Many side table, the many being the type of dimensions, the one side being the Object.

Here is another example with what you gave Length, Width, height, Depth:

Object2 Width 10ft
Object2 Length 10ft
Object2 Height...
Object2 Depth...

The key here is you have a one side table (the object) pointing to a many side table (the dimensions). Some objects can have a dimension others don't have any dimension (a point for example). I don't know your table layout but you could have something to this effect:

Object
ObjectID

Dimension
DimensionID

ObjectDimension
ObjectID
DimensionID
Value

INSERT INTO Object(ObjectID) VALUES("Square");
INSERT INTO Object(ObjectID) VALUES("Point");

INSERT INTO Dimension(DimensionID) VALUES("L"); //for length
INSERT INTO Dimension(DimensionID) VALUES("W"); //for width

INSERT INTO ObjectDimension(ObjectID, DimensionID, Value) VALUES ("Square", "L", 5);
INSERT INTO ObjectDimension(ObjectID, DimensionID, Value) VALUES ("Square", "W", 5);

---

Assuming this database is being created to support an application; I would suggest not worrying about your data-model until you have defined you domain-model first. Start with the application and what it uses these dimensions for. The design a database that will support your domain model. There is simply not enough information here to suggest weather you should create two separate tables with the columns needed, or one table with nullable constraints.

---

If you are using MySQL here is a reference to numeric data types: http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

I'm not sure how large the dimensions are so I can't tell you what it should be. If you're storing the dimensions of a house in millimeters you will need a larger data type. The table on that page will show you though.

For performance reasons. the goal is to try to only store what you think you will need and not more than that.