Sqlite database table: can an array become one column of a record?

Question

I'm designing an iPhone app, which needs to store data into Sqlite database.

One record of the table looks like -

@interface Record:NSObject {
    NSInteger primaryKey;
    NSString *name;
    NSInteger priority;
    NSMutableArray *items;
};

Please be noted one column in the record is an array and the size of the array is not fixed. The size can be zero or any positive integer.

My question is: when I design the Sqlite table, can I specify an array as one column of a record? If NO, How should I design this table? Many thanks!

UPDATE: Thank you for the answer. It seems the answer to my question is NO. If so, can I use other archive methods, say, Property List or NSKeyedArchiver to implement that? Thanks!

Answer 1

You can't do it and be normalized. Your own requirement of size "not fixed" tells you that it's impossible. This is true regardless of how you code it or which data structure you choose. It's the nature of relational databases. You'll want to read about normalization to understand it completely.

Sounds like a one-to-many foreign key relationship. You'll need one table for the Record and another for whatever object ends up in that array. The 2nd table will have a foreign key that points back to the primary key of the first.

I would recommend a more descriptive name than "Record". It seems too generic to me.

Answer 2

You could serialize the array to the disk and store a filename/path in the database for that column.

Obviously you wont be able to query on anything in the array but...