Re: tables by type

[Sasha Pachev]

Stewart Mitchell wrote:
> 
> hello everybody---
> 
> we are working with about 70 or so attributes of mostly char and varchar
> datatypes and
> are attempting to make a schema of not more than 7 entities using these
> attributes...
> because these attributes are provided to us by another data source
> (actually our CAD/CAM
> software), we are free to design entities in any logical way...
> 
> has anyone taken the rather odd step of building tables by putting all
> atttributes of one type in
> one table? that is, all the varchar in one table, all the int in another
> and so forth? the reason this
> question came up is the feature of mySQL of promoting char to
> varchar...if mySQL is going to
> do this, what is wrong with just going ahead straightaway and making
> *all* the attributes in a
> given table  *all* one data type rather than ordering them logically,
> but having different types?
> viz:
> 
> Current Table Design-
> 
> CREATE TABLE FileDataMgt
>       (ItemName                 VARCHAR(80) NOT NULL,
>        ProjName                 VARCHAR(80) NOT NULL,
>        ItemType                 CHAR(80),
>        FileOSName               CHAR(80),
>        LocationOfFiles          CHAR(80),
>        OSFileLength             CHAR(80),
>        FileName                 CHAR(80),
>        FileSize                     INT(11),
>        UpdatedInLibrary         CHAR(80),
>        ItemNumber               INT(5)
> );
> 
> Proposed Table Designs-
> 
> CREATE TABLE FDM_c
>        (ItemName                VARCHAR(80) NOT NULL,
>        ProjName                 VARCHAR(80) NOT NULL,
>         ItemType                 CHAR(80),
>        FileOSName               CHAR(80),
>        LocationOfFiles          CHAR(80),
>        OSFileLength             CHAR(80),
>        FileName                 CHAR(80),
>        UpdatedInLibrary         CHAR(80),
> );
> 
> CREATE TABLE FDM_i
>         (ItemName                VARCHAR(80) NOT NULL,
>        ProjName                 VARCHAR(80) NOT NULL,
>        FileSize                       INT(11),
>        ItemNumber               INT(5)
> );
> 
> The only argument against the proposed design is that is stands the
> idea of logical grouping of one's attributes on its head...the data, BTW
> is
> extracted and cleaned using DataJunction which gives us the flexibility
> of moving the columnar order around any way we want...
> 
> any insights anyone?
> 
> --
> Stewart Mitchell
> 

The only advantage from combining similar types in one
table that I see is to maximize the number of tables
that entirely consist of fixed-length records.

You may also consider replacing chars with ints whenever
possible.

-- 
Sasha Pachev
http://www.sashanet.com/ (home)
http://www.direct1.com/ (work)