[Tutor] Fwd: Which databases allow lists as record fields?

[Alan Gauld]

On 14/03/13 01:39, DoanVietTrungAtGmail wrote:
>     You don't. You create a second table to hold the list.
>     Then in the second table you include be reference back to the first.
>
> assuming I do it that way, how to deal with variable-length list? Most
> lists have 10^3, but some can grow to perhaps 10^7 items. A fixed record
> structure to fit the longest possible record would make the database
> table sparse and, presumably, waste space.

I'm not sure what you mean here but the tables are not fixed size. 
Usually you are only limited by the size of your hard drive.

To give an example if you have an object MyType with two of these list 
type attributes called foo and bar. We have two instances of MyType, A 
and B. A has 10 foos and 50 bars while B has 500 foos and 1000 bars.

Create a table MyType and populate it with A and B

Create a Table MyType_foo and populate it with 10 rows referencing A and 
500 rows referencing B

Create a table Mytype_bar and populate it with 50 rows referencing A and 
1000 rows referencing bar

Now to see all the foos for A use

select value from foo where parent = A

or for B use

select value from foo whee parent = B

and the same for bar...

You can keep adding rows to foo or bar for as long as ypou like.
Youb can add new MyType rows and then add more rows to MyType_foo and 
bar which reference thiose new MyTypes.

There is no limit to how many items you add until your disk fills up!

> An alternative I thought of was to use SQL Server fields for XML, or
> varchar variable-length character strings containing 10^9 characters,
> then to read the list I'd parse the XML or split the string into literal
> integers then convert to int. I wanted to avoid this computation cost if
> possible.

You can do this but you might as well just store everything in XML in a 
flat file. You lose the power of the database query language to 
search/filter the results and give yourself a huge 
development/test/maintenance task.

The people who build databases are likely much better at this stuff that 
you or me. Use what they give you.


-- 
Alan G
Author of the Learn to Program web site
http://www.alan-g.me.uk/