1

Let's suppose I have a list of derived objects from some base:

class Circle : public Shape;
class Rectangle : public Shape;

There is object, which has vector of Shape * as member variable:

class Screen {
    std::vector<Shape *> shapes_;
}

But now I need to persist this in database (PostgreSQL to be precise). Only way I thought of is to have table per class, something like this:

table screen ();

table circle (
    int screen_id;
);

table rectangle (
    int screen_id;
);

And after retrieving from object of type Screen do query for Circles and Rectangles with screen.id. Should be doable.

However I fear that when number of types grows (and it will), it would mean many db queries for just one object retrieval/persist.

Right know we'll have about 10 types I think? That's 11 db queries just to retrieve one Screen object.

So my questing is, is there better way to achieve my goal? I'm fine with redesigning my code if needed, I just would like some O(1) solution in correlation to number of types (my solution is O(n) ).

Thank you guys ^_^

NOTE: based on comment I'm adding that tables of Circles and Rectangles etc. will be used only to put together Screen object. I don't access (look-up, retrieve, persist, ...) to them individually, only as parts of Screen(s). They don't even have to be in their own table. Just didn't find a way to nicely put it all together :/

4
  • That's not that many queries. Commented Oct 3, 2014 at 11:33
  • Is the only reason for storing this in the db persistence? Do you ever inspect this information for other reasons? Do you ever inspect, say, the circle table for any reason other than rebuilding a screen object? Can't answer your question without knowing those things. Commented Oct 3, 2014 at 12:46
  • @itsbruce I added NOTE section to my question. TL;DR no, I only need them for rebuilding screen object. Commented Oct 3, 2014 at 13:00
  • Have you considered using and XML type column? This is typically preferred over the serialized object approach as it allows you the ability to query against the objects dimensions. Commented Oct 3, 2014 at 18:02

1 Answer 1

1

All you need is one table, with a simple design. You need

  1. A field for the uid
  2. Possibly a field for a timestamp (you may need that, you may not, don't know your particular circumstances).
  3. A field in which you serialize the Screen object and all the Shapes it contains.

That means just one write to save and one read to recover the Screen and its shapes. Why do more, given how simple your requirements are?

I recommend you serialize to a json format. Postgresql now has a JSON data type and JSON operators and functions. So you can validate and examine the serialized data in the db if you want to, without having to extract and normalize it.

Other benefits of this approach:

  • If you later decide to serialize somewhere else (into a file or memory cache, for example), it would require minimal change to the code.
  • No need to change your db schema if you add new types of Shapes or change the structure of any existing objects.
6
  • This solution didn't occur to me :D I will serialize only shapes, not screen object. but thanks, I think this will work ^_^ And I will use protocol buffers, they're faster than json Commented Oct 3, 2014 at 18:15
  • This can work, but generally using a SQL database for object persistence involves an ORM layer. Otherwise, you may as well use a true object database or even NoSQL. One of the strengths of SQL is that users can query it offline, populate test data with a script, etc. BLOB fields (e.g. serialized objects) makes this difficult, giving you a database that is purposefully hamstringed. Commented Oct 3, 2014 at 20:04
  • @Snowman, there simply isn't a need for ORM with such a simple case. I did ask if he needed to analyse the serialized data in any way. Since he doesn't, this is all he needs. Hibernate, for example, is a very powerful ORM but would introduce a large amount of complexity and some performance overhead here. Hell, Postgresql is very likely more than he needs unless he's also using that for other things. Commented Oct 3, 2014 at 20:13
  • @itsbruce which is why I mentioned OO and NoSQL databases which are better-suited to the task. Commented Oct 3, 2014 at 20:17
  • Aye, something like MongoDB would be a good fit. Would scale better too, if the application needs to. Commented Oct 3, 2014 at 20:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.