I'm new in this forum, so I'd like to say hello to everyone, first!
As a complete novice in database design, I have been looking for some time for a discussion/introduction/guidelines about data modelling for what I would call "catalogues of heterogeneous collections".
I think, theses kinds of databases share many features, and are common enough, so that I (& others) could learn a lot by studying a detailed discussion about data modelling techniques, design patterns, performance & usability considerations, etc.
But I have found nothing in the web, or in books. Anyone could guide me, where to find such information? Anyone knows an interesting article or book-chapter? Maybe what keywords to look for? (My keywords have proven to be completely useless!)
The data structures I am interested in, show the following recurring characteristics:
Queries are much more frequent than updates, and I want to be able to run complex queries.
Data consists essentially of a list of "properties" of some kind of physical entities. These entities belong to different classes, and each class has it's own set of attributes. In an object-oriented model, you could think of this as different subclasses, with both shared and specific properties.
A few examples should make this more clear:
A media collection catalogue with different kinds of objects, such as books, music records, paintings, and sculptures. Some fields, such as 'author' or 'title' would be shared by all objects, but 'carrier' (canvas) and 'paint' (oil) would apply only to paintings, and not to books, for example.
Anthropological remains. Each kind of bone requires a specific set of metrics (in the order of dozens, each).
A vendor of light bulbs. What kind of technical characteristics and specifications are provided, depends on the type of ligth bulb (if it is incandescent, halogen, fluorescent, LED, plus the many subtypes).
Real Estate: Villa, Warehouse, Building, etc.
To summarize, I see a simple structure of a few independent tables, and each class of objects "makes use" of different tables, depending on the type of the object.
If you are genuinely wanting to get an introduction into relational database design then lower your sights. Model something fixed, concrete, well defined, limited.
If you really want to consider such problems, I think sub and super types, and also EAV\ XML, are things you could investigate. Note that EAV and XML are taking you out of relational design, which is why I suggest you find something more defined.
Oh, sure! I do definitely not intend to work or develop something abstract and highly complex. I want to start very basic. It's just that it usually helps to have an overview and guide from people that have solved many different but related problems. They usually are able to distill best practices, point out pitfalls, and -well- give good advice! At least, I've found many interesting introductory articles in that vein for other DM areas, and was looking for something in this domain. I am still convinced, that I am looking for something rather basic, and well-known in relational database design. Maybe so obvious that no-one really cares to write about it?
I will have a look at the topics you siggested. Thank you!
OK, it seems, I was misleaden about the difficulty of this topic, and that's very good to know. So, if this particular project really takes off, I will ask a professional DB designer to do the job. (This always was an option.)
But it would still be interesting for me to read bout the topic, just to learn...
Sure! A friend asked me to help him (re)implement a catalogue for a collection of bones (at the university). Right now, the "catalogue" is stored in a spreadsheet, and is not of much use, as you can imagine. So, we were looking if we could implement a database (MySQL or PostgreSQL) with an optional web-interface, that would take that catalog a little step further. There is not much money (or interest from the directors) in a big heavy project.
Well that's totally different. If that is your requirement, that is your requirement.
So to bones: these "metrics in the dozens" - are the metrics (not the values) fixed or constantly changing\ being added to? Are they specific to the bone types\ origins or applicable to all?
As far as I understand right now, you have different sets of metrics that can be recorded for different kinds of bones. There are, for example, about 8 sets of cranial metrics (each set with a lot of parameters, or properties). There are other sets for other bones. We wouldn't expect that all sets are recorded for all specimens, yet, but that new records may be added over time. The number/composition of the sets, and what bones they apply to, is expected to be constant, so no major changes to the table-structure is expected. (Always the same 8 sets with the same properties for skulls).
(I can't help but see it similar to real-estate or media-collection )