A question of Database normalization

rojer_31 · #1 (**permalink**) 08-20-07, 02:22

Before my question, I'll give a brief discription of my application.

This is an online registration application to allow people to register and pay for any one of a set of (different types of) programs available. All of the programs have a set of common fields that the registrant has to enter.
Few (very few) programs have a varying set of optional fields (some of which are mandatory).
ie. Some types of programs need fields zzz and yyy. Others need aaa and bbb fields.

Right now, I just have the common set of fields in a single Table. I also have a seperate transaction related table (if that is of any importance).

So my question is, do I create a more 'normalised' database and put these additional fields in a seperate table, or do I just add these additional fields in the main registration table itself and use it only for those that need them?
And if I do use a separate table, how is that to be structured?

I'm sure this little 'problem' has come up before and someone has the perfect solution for this. I just need to know what that is

If it is needed, I could post the table structure as I have it.

mike_bike_kite · #2 (**permalink**) 08-20-07, 05:32

This is a first attempt for you but I haven't done one of these before.
The types for each field are up to you.
The stars indicate key fields.

Mike

Code:

-- stores all your apps
create table Apps (
   *id
   name
)

-- list of possible fields
-- if standard_yn=Y then this field applies to all apps
-- seq_no would decide the oprder it appears on screen
-- assume field_name is unique.
create table Fields(
   *field_name
   standard_yn
   seq_no
   mandatory_yn
)

-- list the non standard fields required for each app
create table AppOptionalFields(
   *app_id
   *field_name
)

-- if the user must pick from a list of values for a field
create table FieldValidValues(
   *field_name
   *value_code
   *value_str
)

-- the users responses for each field for each app.
-- Need to decide if standard fields just appear once per user
-- or are stored each time for each app.
-- This will affect whether app_id allows null or not
create table UserFieldValues(
   *user_id
   *field_name
   *app_id
   field_value
)

-- what ever fields you store for user
create table Users(
   *id
   name
   email
   pwd
   ...
)

-- not sure how you store payments
create table UserAppPayments(
   *user_id
   *app_id
   payment_date
   amount
)

gvee · #3 (**permalink**) 08-20-07, 06:10

Quote:

Originally Posted by rojer 31

So my question is, do I create a more 'normalised' database and put these additional fields in a seperate table,

I reckon I'd run with that idea. This sounds like a situation where a 1:1 relationship should be maintained in more than one table.

r937 · #4 (**permalink**) 08-20-07, 08:11

Quote:

Originally Posted by rojer_31

So my question is, do I create a more 'normalised' database and put these additional fields in a seperate table, or do I just add these additional fields in the main registration table itself and use it only for those that need them?
And if I do use a separate table, how is that to be structured?

I'm sure this little 'problem' has come up before and someone has the perfect solution for this. I just need to know what that is

there is no "perfect" solution, otherwise it would already be out there in the marketplace with a hefty price tag (and perhaps a free 30-day download)

what you have is almost a classic example of a subtype/supertype model (do a search)

your two main options:

1. a single common table with all columns (some of which must be NULLable)

2. a single common table (supertype) with additional related (subtype) tables

the "tipping point" between these two approaches is the ratio of the number of common columns to the number of unique columns

if there are but a few unique columns, use option 1

under no circumstances should you contemplate a design where you store a field name in a column

blindman · #5 (**permalink**) 08-20-07, 09:30

The other big consideration is the frequency with which new optional fields are defined. If you are going to be frequently adding new programs then you need a dynamic solution. Mike has suggested an EAV model. Another option is to use an XML column in the common table and avoid subtables altogether.
None of these solutions is perfect. All of the are awkard to implement in some way or another. Which you choose depends upon the nature of your data.

mike_bike_kite · #6 (**permalink**) 08-20-07, 09:33

Quote:

Originally posted by Rudy
under no circumstances should you contemplate a design where you store a field name in a column

The benifit of following Rudy's advice is being able to use foreign keys which will allow the database to screen bad data at a low level.

The only disadvantage is you'll just need to alter the structure of your database each time you have a new field for an existing application or a new application. This would involve either adding a new field to a table or creating a new table completely depending on your design. Obviously you'll need to redo all your application code as well to cope with these changes.

If you're happy doing all this work then this is definitely the way to go. I personally prefer systems which are compact, quick to write and are designed to cope with future changes. Up to you really

Mike

r937 · #7 (**permalink**) 08-20-07, 09:38

Quote:

Originally Posted by mike_bike_kite

Obviously you'll need to redo all your application code as well to cope with these changes.

that statement is inflammatory, alarmist, and fearmongering

there's no difference between adding a new column to a subtype/supertype model than to any other model -- only the affected portions of the app are affected

mike_bike_kite · #8 (**permalink**) 08-20-07, 09:54

Quote:

that statement is inflammatory, alarmist, and fearmongering

... and true - unless I misunderstand your method.

If you're using new database fields to hold fields for the applications then this involves structural change. If you have structural change in the database then this needs to be matched with relevant changes in the application.

I don't view my solution as EAV (and all the attendant flaming that goes with it) - I'm simply storing the field name in a table which has a many to one relationship with the application. Generic yes - EAV no.

Mike

pootle flump · #9 (**permalink**) 08-20-07, 10:04

It's EAV without the E.

blindman · #10 (**permalink**) 08-20-07, 10:29

Quote:

Originally Posted by r937

only the affected portions of the app are affected

No tautological arguments, please!

r937 · #11 (**permalink**) 08-20-07, 11:08

Quote:

Originally Posted by blindman

No tautological arguments, please!

ordinarily, i would agree with you, but in this instance i was contrasting only affected portions with all your application code

everyone who knows how a VIEW works will understand

no offence intended, i presume everyone here knows how a VIEW works

blindman · #12 (**permalink**) 08-20-07, 11:23

I would say that everyone who understands would understand.

r937 · #13 (**permalink**) 08-20-07, 11:38

that goes without saying, eh

um, wait a sec...

mike_bike_kite · #14 (**permalink**) 08-20-07, 11:42

So if we use your approach to add a field to a new application we just :

create a new table for the application
add new fields when they're required
alter the view to incorperate each new field

While my "AV" approach would be

insert AppOptionalFields values ($my_app_id,'new_field')

I'm not sure I'm convinced yet

Also if you had 500 applications would you need 500 tables to hold the data? I'm not sure this would be very performance friendly.

Mike

EDIT: Added point about performance

r937 · #15 (**permalink**) 08-20-07, 13:02

who was that post in response to?

whose approach would add a table for an application? certainly not mine

i have a data-centric viewpoint towards database modelling, not an application-centric viewpoint

and look, please, i am certainly not out to convince you (i realize how much of an uphill battle that is, and i refuse to do it)

Internet Services

Web Development

Digital Marketing

Consumer Tech