NULL, zero length strings, and empty controls

[Randall Parker]

I am designing a database schema. It happens to be in MySQL but I'm trying to keep it
portable to other databases should the project grow.

Anyway, suppose you have VARCHAR fields and will be using ASP.Net and ADO.Net. In
your experience does it make more sense to allow NULL values for VARCHAR fields or
will they behave well and get set to zero length strings when a user doesn't fill in
a text field?

I'm wondering if I should do:

myfield VARCHAR(200) DEFAULT NULL,

or
myfield VARCHAR(200) NOT NULL DEFAULT "",

I'd rather do the latter. NULL is just one more thing to check for and I'd rather
just check for a zero length string when I'm reading stuff out of the database.

Anyone find the best practice here differs depending on the database chosen or the
front-end layers being used to build on it?

 

[Michel de Becdelièvre]

I am designing a database schema. It happens to be in MySQL but I'm trying
to keep it portable to other databases should the project grow. ....
myfield VARCHAR(200) NOT NULL DEFAULT "",

I'd rather do the latter. NULL is just one more thing to check for and I'd
rather just check for a zero length string when I'm reading stuff out of
the database.

Anyone find the best practice here differs depending on the database
chosen or the front-end layers being used to build on it?

treat '' as null depends on the brand, the version and the installation
setup, or even the session options...

Some databases treat '' as null under the default settings (many half
competent admins won't change the default settings if their life depends on
it), some front end layers will treat '' as null even if the database is
able to make the difference, some databases set this at installation.

So the real question is : have you any CONTROL on the database brand or
setup ? Are you dependent on a database administrator ? Are you hosted ?

 

[Randall Parker]

treat '' as null depends on the brand, the version and the installation
setup, or even the session options...

I guess I need to figure out what MySQL does with zero length strings on an insert.

Some databases treat '' as null under the default settings (many half
competent admins won't change the default settings if their life depends on
it), some front end layers will treat '' as null even if the database is
able to make the difference, some databases set this at installation.

Well, I'm working on a development database. But I have to get my tables added to an
existing production database.

So the real question is : have you any CONTROL on the database brand or
setup ? Are you dependent on a database administrator ? Are you hosted ?

I know the database administrator. I can't change the database brand they use, at
least not quickly.

 

[Bruce Barker]

null really means value unknow. if you are going to treat nulls as a empty
strings, then you should not use null, but default to ''. in most databases
this actually takes less space (supporting null values generally requires an
extra bit.)

-- bruce (sqlwork.com)

 

[Michel de Becdelièvre]

Michel de Becdelièvre wrote:

I know the database administrator. I can't change the database brand they
use, at least not quickly.

As far as I know :
- Oracle always treat '' as null
- SqlServer as an option, on the driver side (if I remember well I have
not used it in a looong time).

try this link (if anyone knows of similarly usefull links, I'm interested) :
http://troels.arvin.dk/db/rdbms/