Database newbie question

[dougo]

I'm just starting to learn about database stuff, playing around with PostgreSQL (and spgsql). So, if I have a data structure that contains an ordered list of another data structure, what's the best way to design the tables? I can think of at least two ways to do it, but I'm not sure I like either: add ordinal numbers to the second table, or store arrays in the first table. In the former, I'd have to renumber if I want to insert something in the middle of a list; in the latter, I'd have to generate a synthetic primary key to use in the arrays. Which I guess isn't so bad, but I don't know how to do this atomically in SQL. Any hints? Is there some other standard idiom for ordered lists? Maybe I need a good online tutorial for database design, any pointers?

Comments

[rikchik.livejournal.com]

I'm not sure I understand the question.

Is it a 1-to-many relationship with the many needing to be in a particular order? Or is it many-to-many? Either way, I think I'd go with ordinals somewhere, either in the many table or the relationship table.

[dougo.livejournal.com]

1-to-many, ordered.