CODEWITHSUNDEEP
ms-accessdatabase-design
YvetteLee
YvetteLee

Reputation: 1090

Ms Access Table Design - list or new table

I have a table:

tblA
ID    = Autonumber
Price = Number
Name  = Text

The field [Name] contains values from a list. The list has 2-3 values.

I will add 2more values in the list, so total 5values.

What is better for performance for my database. The field to be list or create another table and place there the values? What is better for database size and speed?

New Design:

tblA                                   tblB
ID     = Autonumber                    NameID = Autonumber
Price  = Number                        Name   = Text
NameID = Number

Thank you.

Upvotes: 0

Views: 153

Answers (1)

Santosh
Santosh

Reputation: 12353

A lookup table is better than creating a Value list.

  1. A lookup table is easy to maintain whereas as Value list are kind of hardcoded and gets difficult especially when the list grows gradually.

  2. You can add sort_by Number field in tblB for custom sorting the list

  3. You can add inactive Yes/No field in tblB to easily turn off any value

  4. New table design will reduce the database size as it avoids data redundancy.

  5. Set Name as primary key in tblB which will automatically create index and help to speed up. Speed impact would be more seen in case database grows huge or poor network connection.

Upvotes: 1

Related Questions