Normalization Question

[PJFry]

I am working on database normalization and I have a theoretical question.

I have a table with 1000 records and two fields. Let's call it tOrders. The
first field, OrderID is the primary key. The second field, Location, can be
one of four options, TW, FF, EQ or SN. Like so:

pkOrderID txtLocation
10001 TW
10002 TW
10003 FF
10004 SN
10005 SN
10006 EQ

To propery normalize this, I would create a second table, tLocation with
four records and a primary key to identify those. Something like this:

pkID txtLocation
1 TW
2 FF
3 EQ
4 SN

Now how do I relate them? Would the table with the OrderID look like this:

OrderID lngLocation
10001 1
10002 1
10003 2
10004 4
10005 4
10006 3

with a join on tLocation.pkID = tOrders.lngLocation?

 

[Klatuu]

Yes

 

[Pat Hartman \(MVP\)]

Yep, that's how it works. I recommend using the same name for PK/FK pairs.
So the field would be called LocationID in both tables. This makes it
significantly easier to look at your schema and see how it fits together.
All PK's end in ID and the FK's that reference them have identical names.
You only need to deviate from this if multiple FK's in a single record point
to the same PK. An example of this would be EmployeeID and SupervisorID.
EmployeeID is the PK of the employee table and it includes a field -
SupervisorID that references the EmployeeID of a different record since
supervisors are employees also.

 

[J_Goddard via AccessMonster.com]

Hi -

You have the right idea. If txtLocation is unique within tLocation, you
don't really need the numeric pkID - txtLocation is already the primary key.
There no rule (at least not that I know about) that says a primary key must
be numeric.

So, you can leave tblOrders as it is, and have tLocation contain the details
of the locations. In a query, the join would be on tOrders.txtLocation =
tLocation.txtLocation

John

 

[PJFry]

Thanks all for the input. Very helpful!

Hi -

You have the right idea. If txtLocation is unique within tLocation, you
don't really need the numeric pkID - txtLocation is already the primary key.
There no rule (at least not that I know about) that says a primary key must
be numeric.

So, you can leave tblOrders as it is, and have tLocation contain the details
of the locations. In a query, the join would be on tOrders.txtLocation =
tLocation.txtLocation

John

 

[Jamie Collins]

I am working on database normalization and I have a theoretical question.

I have a table with 1000 records and two fields. Let's call it tOrders. The
first field, OrderID is the primary key. The second field, Location, can be
one of four options, TW, FF, EQ or SN. Like so:

pkOrderID txtLocation
10001 TW
10002 TW
10003 FF
10004 SN
10005 SN
10006 EQ

To propery normalize this...

But this table is already normalized!

I would create a second table, tLocation with
four records and a primary key to identify those. Something like this:

pkID txtLocation
1 TW
2 FF
3 EQ
4 SN

Now how do I relate them? Would the table with the OrderID look like this:

OrderID lngLocation
10001 1
10002 1
10003 2
10004 4
10005 4
10006 3

with a join on tLocation.pkID = tOrders.lngLocation?

All you've achieved is replacing the natural key with a so-called
surrogate. Whether that is a good idea or not is essential a matter of
style; I think you'll find that the others in this thread are agreeing
with you because your revised design fits their personal style (I
prefer the original design because it makes the tOrders data easier to
read) and are not suggesting that you will attained a higher normal
form in doing so.

Jamie.

--

 

[scubadiver]

It depends if a location can have more than one order?

Then you would need two primary tables and a junction table

tble_order
OrderID (PK)

tble_junction
OrderID (FK)
LocationID (FK)

tble_location
LocationID (PK)

Choose a primary table to put in the main form, the junction table in the
subform and use the other primary table as the source for the appropriate
field in the junction table (if that makes sense!)