Are user defined SQL data types used?

My DBA told me to use a custom SQL datatype to represent addresses and then use a single column of that new type in our users table instead of multiple address columns. I've never done this before and I'm wondering if this is a general approach.

Also, what is the best place to get information about this - is it a specific product?

+2


source to share


4 answers


There are a few more questions on SO on how to represent addresses in a database. AFAICR, none of them offer a user-defined type for this purpose. I would not see it as a general approach; this does not mean that this is not a reasonable approach. The main difficulties are deciding what methods should provide manipulation of address data - those used to format data that should appear on an envelope, or in certain places in printed form, or to update fields, worrying about the many implications of international addresses. etc.



Defining custom types is very product specific. The way you do it in Informix is โ€‹โ€‹different from how you do it in DB2 and Oracle, for example.

+1


source


As far as I can tell, at least in the SQL Server world, UDTs aren't used very much.

The problem with UDTs is that you cannot update them easily. Created and used in databases, they almost look like stone.

There is no CREATE OR ALTER (UDT) command :-( So in order to change something you need to shuffle a lot - perhaps copy existing data, then drop a lot of columns from other tables, then omit the UDT, recreating it with the new structure and reapplying the data and that's it.



It's too much of a hassle - and you know: there will be a change!

Right now in the land of SQL Server, UDT is just a good idea, but very poorly implemented. I would not recommend using them extensively.

Mark

+3


source


I would also prefer to avoid using custom datatypes as their security and usability will make your code database specific.

Instead, if you are using any kind of object oriented language, create a compositional relationship to define addresses for an employee (for example) and store the addresses in a separate table.

Eg. Employees table and Employee_Addresses. One employee can have several addresses.

0


source


SQL user-defined datatype for representing addresses

Custom types can be quite useful, but the postal address doesn't jump out as one of those cases (at least for me). What is the mailing address for you? Is it something that you print on an envelope to send to someone? If so, the text is about as good as it gets. If you need to know what state someone is in for legal reasons, keep it separate and that's not a problem.

Other posts here criticize UDT, but I think they have some amazing capabilities. PostgreSQL has had full text search as a UDT-based plugin for a long time before full text search was actually integrated into the mainstream. PostGIS is currently a very successful GIS product, a completely UDT-based plugin (it is GPL licensed, so it will never be integrated into the core).

0


source







All Articles