30 columns in a table shouldn't be a problem, as long as there is no real duplication of data (meaning that there is no way to normalize the data by splitting it into 2 or more tables).

Your option 2 gives you a lot of flexibility, but makes retrieving the data that relates to a single source record rather painful (multi-table joins). If your application is going to fetch this data a lot then I wouldn't use that schema.

Option 3 works well, as long as you don't need to refer to the serialized data in any SQL statement (i.e. in a WHERE clause, for example.). An alternative to serializing the data is to store an XML block with the additional "columns", something that is becoming fairly common, with DBMS engines that are able to generate pseudo columns from the XML with an internal XML parser.

All in all my hunch is to go with the most straightforward solution (your Option 1), though I obviously don't know all the details of the app to gauge whether this might be too limiting.

Michael

In reply to Re: (OT) Database design: omitted partially filled fields. by mpeppler
in thread (OT) Database design: omitted partially filled fields. by BUU

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


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.