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 ;-)

Replies are listed 'Best First'.
Re: OT: defining DB fields - working towards consistancy...
by Abigail-II (Bishop) on Jul 15, 2003 at 08:44 UTC
    The traditional way of doing such things is to make a table that contains all the domains, another with all the ip addresses, and tables like vhost refer to the tables with the domains and ip addresses using foreign keys. That way, all your domains have the same type, and so have your ip addresses. Furthermore, it enables you to do queries targetted at domains or ip addresses, so it's easy to find a maximum length.

    You are trying to apply structured programming techniques on relational databases. That's not going to work - you need to apply relational techniques. Apply the right tools in each domain.

    Abigail

[reply]
      Ahhh, the ideal world :)

      cLive ;-)

[reply]
      Normalization is good, but it does not eliminate the usefulness of data typing whether that typing is weak aliasing or more real. Both are worth pursuit.

      I don't know what MySQL provides as support for user defined types. I don't believe that any such support will be standard SQL.

[reply]
      Howdy!

      Abigail-II wrote:
      The traditional way of doing such things is to make a table that contains all the domains...

      On the other hand, it may be the case that the tables are appropriately normalized (ignoring the physical data types) but the implementation was done with less care than one might like...

      yours,
      Michael

[reply]
        Uhm, no. That would be impossible in any database I know of. If the tables were appropriately normalized there would be exactly one column, in exactly one table that hold the domains. I don't any database were a column can have more than one type, and if there is one, I don't see how you can manage to define multiple types with "less care".

        Abigail

[reply]
Re: OT: defining DB fields - working towards consistancy...
by mpeppler (Vicar) on Jul 15, 2003 at 12:23 UTC
    Sybase (and, I suppose, MS-SQL) lets you create user-defined data types like so: 
    sp_addtype <typename>, <system type name>

    [download]
    and you can then use the type in any place where you normally use a system data type. You can also bind rules and defaults to the user-defined data type.

    As for going through the database catalog to find the offending column names/types you would do that with a query agains sysobjects, syscolumns and systypes, where sysobjects holds the name of all the objects in the database (in this case the tables i.e. where sysobjects.type = 'U'), syscolumns holds the name of all the columns in a table, and finally systypes holds the definition of the data type for each column.

    AFAIK there is no pre-cooked system function or stored procedure that will fetch this information for you, but the query is relatively easy to write.

    That being said I agree with Abigail-II - you should really have that data normalized... :-)

    Michael

[reply]
[d/l]
      Thanks, just what I was looking for!

      cLive ;-)

[reply]

Back to Meditations