Slightly OT, but either I can't find anything about this because I'm searching on the wrong terms, or it doesn't exist.

I'm going through a bunch of MySQL DB tables. Several of them have a column called 'domain' (web domain name). Some are set as varchar(60), some varchar(90) etc - you get the idea. Consistancy's a little off.

So, as part of my tidying up, I'm ensuring they all get the same definition (erring on the side of caution).

But what I'm wondering is can you (and if not, why not), create a custom data type as a variable somewhere in the database, and then refer to that when defining a commonly used field. ie, something along the lines of (pseudocode!): 

DEF $DOMAIN = VARCHAR(90) NOT NULL DEFAULT '';
INFO $DOMAIN = 'website domain name';
DEF $IP = VARCHAR(15) NOT NULL DEFAULT '';
INFO $IP = 'IP4 Address';

# etc, then, when creating tables, use
CREATE TABLE vhosts (
    domain $DOMAIN_DEF PRIMARY KEY,
    ip $IP_DEF
);

[download]

With me so far? Good :)

And then, be able to run some kind of query on the db that would parse all values for fields defined using a definition and report on the *actual* maximum length currently in use (to help with redefining the field definition later).

# pseudo
ANALYZE DATA WHERE DEFINITION IS $DOMAIN_DEF;

[download]

Another good thingy would be to be able to redefine the definition on the fly, but ONLY if it affected no values (and die listing tables and records that would be affected if values would be changed). 

# pseudo
UNLESS ERR REDEFINE $DOMAIN_DEF = VARCHAR(80) NOT NULL 
DEFAULT '';

[download]

Do any databases have anything like this? It would just be so useful to have a reference table of definitions and explanations in plain english.

I know I could script a lot of this, but I wondered if there was anything like this already built into any databases.

Or am I just living in my own little dream world .oO(?)

cLive ;-)

In reply to OT: defining DB fields - working towards consistancy... by cLive ;-)

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.