Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

comment on

( [id://3333]=superdoc: print w/replies, xml ) Need Help??
A table cross-referencing each row from the class table with rows from the attributes table. This kind of table would contain three columns:
EntityAttributeValue

I've used this approach myself, but it comes with some drawbacks.

By coercing all values into a single column, you lose datatype information. If the optional data (or sparse data, let's say, since optional data that is dense is probably better stored directly in the table anyway) is all of one type this isn't really a problem. But if numerics and dates are sprinkled in, the design gives up the ability to easily construct SQL statements involving conditions on those fields. Views can help with this to an extent.

This structure is not convenient for storing both attributes that may have one value and attributes that may have many. This choice represents either a unique key pair of object and attribute or no key. An additional column for attribute 'order' would allow a unique key in these circumstances, but is unnecessary for single-value attributes. A bit of a data-integrity headache. I used two tables (one for sparse single-valued- and one for sparse multiple-valued-attributes).

I don't have a ton of experience across opensource RDBMS, but what little I have tells me join speeds vary greatly. YMMV.

...adding new attributes can be done on the fly. Storing the optional attributes as columns requires a database change for every new attribute.

Indeed, this is really the only way I've seen 'End-User' specified attributes handled in RDBMS without allowing schema changes. If anyone has seen different implementations, I'd be interested in hearing about or seeing them.

--Solo
--
You said you wanted to be around when I made a mistake; well, this could be it, sweetheart.

In reply to Re: OO concepts and relational databases by Solo
in thread OO concepts and relational databases by dragonchild

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Are you posting in the right place? Check out Where do I post X? to know for sure.
  • Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
    <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
  • Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
  • Want more info? How to link or How to display code and escape characters are good places to start.
Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others chanting in the Monastery: (4)
As of 2024-03-28 21:09 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found