• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

database design issue: an explosion of tiny tables to store simple fields?

 
Benjamin Weaver
Ranch Hand
Posts: 161
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am working with text objects. A text object will have lots of fields that are potentially multiple. There may be more than one author, more than one modern editor, more than one edition number, etc.

These potentially multiple fields are nothing more than strings.

For my purposes, multiple strings of this kind would consist of composed/aggregated objects stored as a member in an instance of a MyTextObject. The member would be some kind of collection. Like this:




My question: is there any legitimate way around creating lots of tiny tables, one for each multiple field, when the multiple fields are nothing more than strings?

We will be doing lots of involved queries, and although I have a fair amount of experience with SQL, this profusion of tables would be a pain to implement. But I don't see any way around creating them when the relation between the MyTextObject and the various fields is one-to-many.
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can also always make one table with three fields.

1. Surrogate Primary Key
2. Discriminator Field
3. Value

Mark
 
Benjamin Weaver
Ranch Hand
Posts: 161
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks, in fact I hit on the same solution just last night: a flattening table or view would make queries easier.

This would seem the easier. It has also been suggested to me, however, to use SQL arrays. What do you think of this solution? I don't know about the performance of arrays. We would not be working with more than 50,000 to 100,000 rows in the database.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic