database structure

tags:

database-design

views:

answers:

+1 Q:

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:

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
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.

+2 A:

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

Dänu 2010-05-29 20:11:50

the third approach sounds good to me actually, because then I would have a separation of my activityrecording and forms.

2010-05-29 20:20:00

yeah that's how I would do it, after all its kinda mvc, you have the view (form) separated from the table (model) if you make a change to the view, you wouldn't have to change anything on the model (table)

Dänu 2010-05-30 16:13:04

So, if I make a separate table with all these fields (from all the 6 forms) then is it a good design because for every row I will have 80% of the columns null always.

2010-06-03 03:02:14

+2 A:

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.

dportas 2010-05-29 23:02:28

Here is some information about the Data: I have ActivityRecording table which right now stores data about the service that was provided (just the service code), the time of the service, the staff member who provided the service and this kind of information. Now we need to capture details about this service (right now we are not capturing any details about this service), like the response of the client, the comments from staff and some check boxes etc. Different services have different kinds of forms (some of them share the same form) and the total we have is 6 different kinds of forms.

2010-05-30 16:35:06

+2 A:

You missed another option.

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

Anthony Pegram 2010-05-29 23:15:52

Yes this is another option, though it won't make any difference, I guess.

2010-05-30 16:40:17

ansaurus

tags:

views:

answers:

database structure

related questions