The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Multi value fields?

Does any DBMS like Oracle or SQL server support multi value fields, row fields, ref fields etc yet? or is it still boo boo?

I read the SQL 99 standard and it seems to support multi value fields etc but I am not able to google up any page where these DBMS support multi value fields?  Any links for me to look at?
Wednesday, December 17, 2008
You can define an array or a nested table as part of a table in Oracle.  I wouldn't, but you can.  The array is limited to 4000 entries but the nested table is not.

Personally you are better off using standard design and have a view or a object view over the standard bits.
jim Send private email
Wednesday, December 17, 2008

but lessee ... Access supports them, and SQL Server 2005 or 2008 does as well I believe.
Andrew Badera Send private email
Thursday, December 18, 2008
Array and Nested tables were the words I needed to know to search for. Thanks.

Exactly why is it evil?
Friday, December 19, 2008
> exactly why is it evil?

Because it breaks the rules of data normalisation. If you have a field in a table which repeats, you are supposed to split it off into its own table, with a separate row for each repeatable value. If you want to find a occurrence of a particular value it is far easier to search through rows in a table than it is to search through an array in a table.

For example, an order has a header and one or more order lines, so they are typically separated out into their own tables. This makes the query "show me all orders for product X in the last month" very easy. If you held the order lines as an array in the order header this would be *much* more difficult.
Tony Marston Send private email
Saturday, December 20, 2008
I agree with Tony.  Also to do the array and nested table "magic" Oracle adds a bunch of extra invisible things to do it.  Thus the actual storage has overhead and other implications that may bite you later on.  If it is an array you can't search it via sql. 

You would be better to do it the traditional way that Tony describes and if you really really really have to do it then create an object view on top of your normal relational tables.
Jim Send private email
Saturday, December 20, 2008

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz