mysql - SQL Store different types of objects in a single table -

mysql - SQL Store different types of objects in a single table -

i trying define database schema store list of "objects" in table.

the main problem need have list of geometric shapes , other kinds of markers in table, in order draw them on google map.

for example, circle have x,y , radius. polyline have set of points, , square have...etc etc...

my first thought create 2 related tables like:

object

id name

object_details

id object_id (foreign object) attribute_type ( enum cartesian_point, size etc....) numeric_attribute_valuea numeric_attribute_valueb datetime_attribute_value etc...

but ugly , inefficent implementation because may need have more different attribute info types - text or time or else , complicate schema creating empty rows in object_details table.

i inquire sentiment problem.

what good/clean solution problem?

regards

you'll have pack info in format, store it, , unpack later use. example, json, supported both php , js. illustration in php:

$shape = array("kind"=>"circle", "cx"=>45,"cy"=>90,"r"=>20); $json = json_encode($shape, json_numeric_check); // numeric_check doesn't treat numbers strings

sql:

$query = "insert shapes set `shape`=>'$json'";

you can php array "json_decode();"

$shape = json_decode($json);

also, format makes easy utilize ajax , jquery, server can deliver json contents directly:

$.getjson("server.php",function(response) { console.log("kind " + response.kind + ", radius " + response.r); });

mysql sql