CODEWITHSUNDEEP
database-design
Yogesh Jindal
Yogesh Jindal

Reputation: 592

database structure

I have a table named ActivityRecording. This table currently has 500,000 records. I need to add a lot of new inputs that relates to activityrecording table. The relation of activityrecording with these new input fields is 1 to 0,1.

So, what's going to happen on screen is when user fills the ActivityRecording data, he will then be taken to a new page and this page will show a form based on the user's input (from a dropdown named service) in activityrecording. There will 6 different kinds of form (each form will have 7-8 inputs which includes textareas of size 5kb, textboxes and checkboxes). So, for one activityrecording user will fill one out of 6 forms.

There are two ways I know (there could be more), I can design the data structure:

  1. Add all the inputs from all these 6 forms into the activityrecording table. So, columns belonging to 5 of these forms will be null in this table, only columns belonging to one of the forms will have values

  2. The other way would be add 6 new tables (one for each form) and add 6 foreign key columns to activityrecording table. So, out of 6 foreign keys, 5 will be null and one will actually point to a table

Which approach is a better data structure design? Please take into consideration that number of rows in this table are 500,000 and are expected to grow at a faster rate now.

Upvotes: 1

Views: 192

Answers (3)

Anthony Pegram
Anthony Pegram

Reputation: 126864

You missed another option.

No changes to ActivityRecording table. Foreign Key field for ActivityRecording in each of the supplemental tables

Upvotes: 2

nvogel
nvogel

Reputation: 25526

Forms and user interfaces have nothing to do with sound database design.

Be guided by basic design rules like Normal Form and by understanding the basic properties of the data you are trying to store. There's almost no information about the data in your question - it's all about forms! Therefore it isn't possible to give a good answer about a specific design.

Upvotes: 3

Dänu
Dänu

Reputation: 5929

You should ask yourself: "What will happen if I need one more form." (you'd have to create a new table). In my opinion the first approach would be the better, since I don't like creating new tables (you'd have to update your linq classes etc etc).

There's a 3th one as well, create a table for the forms and link them to the input table. forms 1 ----- n input

Upvotes: 2

Related Questions