Re: Is a relational database appropriate?

Originally posted by Scott F. Crosby
> Hello,
> I have a question about whether or not a relational database is
> appropriate for a project I'm working on. Most of the data in
> the project
> is fairly simple; a large number of fairly simple tables with easy to
> moderately complex joins. Nothing fancy. But I would like to store
> information about objects that have a large number of common
> characteristics, but an even larger number of UNCOMMON
> characteristics.
> An example will help to better explain what I mean.
>
> Let's use people. For each person, there are certain mandatory
> characteristics; height, weight, age, location, etc.. This is
> all stored
> in a single table. But I also have a large, dynamic,
> ever-changing list
> of other attributes, attributes that apply to some people but not all.
> These include things like profession, job skills, favorite
> color, etc..
> Some of these attributes are boolean (can they whistle or not)
> and some
> require values (income.)
> My current solution is to keep a third table with three columns;
> PersonID, TraitID, and Value, where value is -1 for boolean
> types. This
> works fine, but can get cumbersome, particularly if manual
> manipulation of
> the third table becomes necessary.
> So my question is, is there a better way to do this? Would a
> "hierarchical" database be more appropriate for this small
> subset of the
> project? Could a hierarchical database be implemented using SQL?
> This application is all currently being designed in C, but I
> think it
> might be easier to implement using something like MS access.
> Unfortunately, I have not figured out a way to use the
> three-table method
> described above efficiently in access. Even something as simple as a
> multiple-selection box would work (for the boolean types,
> anyway. Almost
> all of these "extra" attributes are boolean, and those that aren't
> actually need to be done away with, anyway.)
> Any thoughts?
See the posting entitled "What is this model technique called" about 3 down from yours for a discussion of this "3 table" approach.

Whether you really need to be able to invent new attributes on a whim on a daily basis is questionable anyway. It sounds like "we can't be bothered to define the data model now, let's allow the users to add anything they like, whenever they like".

--
Posted via http://dbforums.com