Normalization: How to deal with a field that does not have any associated data

[Ayat]

Hello.

I have a simple modeling question. Lets say I have the following table structure:

Table 1
-------
Cust_id
Cust_name
Phone
City
Supplier_id
Dept

Table 2
-------
Supplier_id
Supplier_name
phone
City

Table 3
-------
Dept


Table 1 is M-to-1 with Table 3. Table 2 is 1-to-M with Table 1.

I have seen such as schema and do not understand why there needs to be a separate Table 3. Can someone explain why Table 1 alone would not suffice and Table 3 would not be required?

Basically, can someone explain the rationale for normalizing tables when there do not exist sufficient associated data for the field for which a new table is created (i.e. in this example, if Dept had a lot of other fileds associated with it such as Dept_desc, Dept_Mgr etc , then I can see the value in creating a different Dept table and joining it with Cust table through a Dept_id field).

A similar such design exists in the standard "Address Book" sample schema that ships with Access (the analog field in question there is Role).

Thank you for your response.

 

[Len Boorman]

Basically table 3.Dept is acting as a control to what can be appear in Table 1


There are generally two ways of doing this.

Having a list of dedicated values within teh Definition of Table 1.Dept which requires table in design view to add another value or

having another table like this where referential integrity can be enforced. Another value for Dept requires only an entry into table.3.Dept

Simple to administer and does not need table in design view or direct table access at all

Use it a lot myself to control entries but also to allocate responsibility of acceptable values to a Core User

Len

 

[neileg]

One use for Table 3 is to enforce consistency. Using a table and a combo box with the limit to list property set to yes, forces the user to select a predefined deparment, and to ensure the spelling, etc, is correct.

Now I would define Table3 with and ID and description, and to hold the ID as the FK in Table1. Department names can change over time, this would enable the changes to be associated automatically. It also allows the addition of fields as you suggest in a later design change.

 

[Ayat]

thanks for the replies.

In line with this idea, I have designed a schema with several normalized tables and will put it shortly on this board for your take on it. Thanks