What should one map strings to in a database ORM?

Strings are unbounded, but it seems every normal relational database requires that a column declare its maximum length. This seems to be a rather significant discrepancy and I'm curious how typical ORMs handle this.

Using a 'text' column type would theoretically give you much more string-like storage, but as I understand it text columns are not queryable, or at least not efficiently (non-indexed?).

I'm thinking of using something like NHibernate perhaps, but my ORM needs are relatively simple, so if I can just write it myself it might save some bloat.

From stackoverflow

• According to my knowledge, they dont handle it, either you let the ORM define the schema, then: The ORM will decide the size either defaulted or defined by your config.

  Or the schema is not deined by the ORM, then it just has to obey the rules, if you insert too large strings, then you'll get errors from the DB.

  I would stay on varcharish types, e.g. varchar2 for oracle or nvarchar for sql server, unless you're dealing with clobs.

• SqlServer for instance stores only the actual size of the data. For me, defining a large enough size for strings is sufficient that the user doesn't recognize the limits.

  Exmaples:

  • name of a product, person etc: 500
  • Paths, Urls etc: 1000
  • Comments, free text: 2000 or even more

  NHibernate does not anything with the size at runtime. You need to use some kind of validator or let the database either cut or throw an exception.

  Quote: "my ORM needs are relatively simple". It's hard to say if NHibernate is overkill or not. Data access isn't generally that simple.

  As a simple guide of the top of my head, take NHibernate if:

  • You have a fine-grained or complex domain model. You need to map inheritance.
  • You want your domain model somewhat independent from the database model.
  • You need some lazy loading features
  • You want to be database independent, eg. run it on SqlServer or Oracle

  If you think that a class per table is what you need, you don't actually need a ORM.

  chaiguy : "SqlServer for instance stores only the actual size of the data." --Does this mean I could theoretically declare the max size to be something like MAX_INT or would that still have some performance drawback?

  Stefan Steinegger : NVarchar It is limited to 8000 - then you have to specify nvarchar(MAX), which is actually a blob with some special features like the possibility to filter. Can't say for 100% if there is no performance drawback at all, but I don't know why there should be one. On the other hand it doesn't make sense to have a user name of 8000 characters, because you couldn't display it properly on the screen or on reports. So limiting to reasonable values doesn't hurt at all.