I have many different objects having a different quantity of characteristics. So far I've saved the information in XML files which easily permit a constantly altering quantity of characteristics. However I am attempting to move it to some database.
An amount become your preferred method to store this data?
A couple of methods I've recognized to date:
- Getting a single area named "characteristics" within the object's table and keep data serialized or json'ed inside.
- Storing the information in 2 tables (objects, characteristics) and taking advantage of another in order to save the relations, which makes it a genuine n:m relation. Very clean solution, but possibly very costly to fetch a whole object and all sorts of its characteristics
- Determining characteristics all objects share and creating fields of these towards the object's table. Keep remaining characteristics as serialized data in another area. It has a benefit within the first strategy, making searches simpler.
Should you ever intend on trying to find specific attribtes, it's an awful idea to serialize them right into a single column, since you will need to use per-row functions to obtain the information out - this never scales well.
I'd go for your next choice. Have a listing of characteristics within an attribute table, the objects in their own individual table, along with a many-to-many relationship table known as object characteristics.
objects: object_id integer object_name varchar(20) primary key (object_id) attributes: attr_id integer attr_name varchar(20) primary key (attr_id) object_attributes: object_id integer references (objects.object_id) attr_id integer references (attributes.attr_id) primary key (object_id,attr_id)
Your worry about performance is noted but, in my opinion, it certainly is more pricey to separate a column rather than mix multiple posts. Whether it works out that you will find performance problems, it's perfectly acceptable to interrupt 3NF for performance reasons.
For the reason that situation I'd store it exactly the same way but in addition have a column using the raw serialized data. Provided you utilize place/update triggers to help keep the columnar and combined data synchronized, you will not have problems. However, you should not be worried about that until a real problem surfaces.
By utilizing individuals triggers, you minimize the job needed to simply once the data changes. If you attempt to extract sub-column information, you need to do unnecessary focus on every choose.
I did previously implement this scheme:
t_class (id RAW(16), parent RAW(16)) -- holds class hierachy. t_property (class RAW(16), property VARCHAR) -- holds class members. t_declaration (id RAW(16), class RAW(16)) -- hold GUIDs and types of all class instances t_instance (id RAW(16), class RAW(16), property VARCHAR2(100), textvalue VARCHAR2(200), intvalue INT, doublevalue DOUBLE, datevalue DATE) -- holds 'common' properties t_class1 (id RAW(16), amount DOUBLE, source RAW(16), destination RAW(16)) -- holds 'fast' properties for class1. t_class2 (id RAW(16), comment VARCHAR2(200)) -- holds 'fast' properties for class2 --- etc.
RAW(16) is how
If you wish to choose all qualities to have an object, you problem:
SELECT i.* FROM ( SELECT id FROM t_class START WITH id = (SELECT class FROM t_declaration WHERE id = :object_id) CONNECT BY parent = PRIOR id ) c JOIN property p ON p.class = c.id LEFT JOIN t_instance i ON i.id = :object_id AND i.class = p.class AND i.property = p.property
t_property hold items you normally don't explore (like, text explanations etc.)
Fast qualities are actually normal tables you've within the database, to create the queries efficient. They hold values just for the events of the certain class or its descendants. This really is to prevent extra joins.
It's not necessary to use fast tables and limit all of your data to those four tables.