seperate table or one table to hold all the drop down options?

[Thinh]

i can create different table to hold the drop down options for different form or i can create a structure like this to do the same thing.

Table: DropDown
DropDownID, AutoNumber
DropDownOption, Text

Table: DropDownItem
DropDownItemID,AutoNumber
DropDownID, FK DropDown
ItemNr, Number
Item, Text

Sample Data: DownDown
DropDownID,DropDownOption
1,Section
2,Temp
3,Temp Type

Sample Data: DropDownItem
DropDownItemID,DropDownID,ItemNr,Item
1,1,0,Section
2,1,1,Head
3,1,2,Body
4,1,3,Tail
5,2,0,Temp
6,2,1,5
7,2,2,10
8,2,3,15
9,2,4,20
10,3,0,Temp Type
11,3,1,C
12,3,2,F

and so on

Which approach is better in the long run and from administration standpoint.
multiple small table vs One Huge table

 

[gemma-the-husky]

i use separate tables, because some dropdowns need more fields that others, and the fields may be of different types.

ie a lookup for a unit of sale might carry

unitid
description
quantity
scale factor
discreteflag

but a lookup for a processing status might be just

statusid
statusdesc

its easier to have separate tables.

 

[Simon_MT]

I you are just providing descriptive narratives and nothing else (Don't need to be linked into Queries) you can use one table. The only reason I did this is that my application ended with more descriptive (non-data) tables that actual data tables.

I would create separate tables if the information is used in queries and attached to a data table or they are critical to the application. Statuses can be crucial and do not that informing users of e.g. saleability, but this is in a separate file.

My rule if you have only several fields like Code and Description then consolidate with

Type
Code
Desc

Select Type in Lookup for the differing Categories.

Simon
Simon

 

[Thinh]

I forgot to mention how are things from administrator perspective? it most be hard to delete or add something since this is a matrix.

 

[gemma-the-husky]

a matrix?

its just a table of possible values, with a type identifier of some sort to enable you to group and select them

 

[Simon_MT]

The whole point is that these are descriptors and system orientated and not for user amendment. Changeable characteristics are in a proper table structure.

Simon

 

[Thinh]

What better to store in a table the primary Key or the actual text?
There is pros and cons for both implementation. i just want to get best practice.

Primary Key as storing field
Pros:
faster search(using the full name, if they use Like statement this doesn't apply)
Change one place and everything will change according to the new Change.
follow 3rd Normal form.
Cons:
more queries since you need additional lookup to translate number back into
their text value.
Hard to understand data since its number in table view. Past data will change to the new change.

I am in the cross road between the two implementation.

 

[gemma-the-husky]

dont use a text field as a primary key

its inefficient, and it might change


otherwise i don't really understand what you are saying/asking

 

[Simon_MT]

Combi.rowsources loaded On Enter

Example 1:

OOC = Oil on canvas
OOB = Oil on board

Short key (FK) Primary Key, Unique

Input of OOC is quicker as users can remember the code without referring to the downdrop list, no-one can remember a number, an abbreviation is more effective. It is also quicker than use the full description.

Function LookupMedium()
    With Screen.ActiveControl
        .RowSource = "SELECT Mediums.Medium, Mediums.[Medium Desc] FROM Mediums ORDER BY Mediums.Medium;"
        Call ListDisplay
    End With
End Function

Example 2:

Artists have a Category and Style

Table Artists Categories
Query ArtistsCategories

Artist Cat Type
Artist Cat
Artist Cat Desc

Artist Cat Type + Artist Cat PK

Function LookupArtistsCategory()
    With Screen.ActiveControl
        .RowSource = "SELECT ArtistsCategories.[Artist Cat], ArtistsCategories.[Artist Cat Desc], ArtistsCategories.[Artist Cat Type] FROM ArtistsCategories WHERE ArtistsCategories.[Artist Cat Type]='C';"
        Call ListDisplay
    End With
End Function

Function LookupArtistsStyle()
    With Screen.ActiveControl
        .RowSource = "SELECT ArtistsCategories.[Artist Cat], ArtistsCategories.[Artist Cat Desc], ArtistsCategories.[Artist Cat Type] FROM ArtistsCategories WHERE ArtistsCategories.[Artist Cat Type]='S';"
        Call ListDisplay
    End With
End Function

The former is important and proper data, the later is more notational.

Simon

 

[Thinh]

I have a table that uses a drop down menu let me illustrate my point a little more.

Option 1:
Table - Sales Order:
SalesOrderID, primary Key-Autonumber
CustomerID, long

Options 2:
SalesOrderID, Primary Key-Autonumber
CustomerName, text

Option 1:
Table - Customer:
CustomerID, PrimaryKey-Autonumber
CustomerName, Text

Option 2:
Table - Customer:
CustomerName, Text

These will be value written in table depending on what option you choose

Option 1:
Table - Sales Order:
SalesOrderID = 1
CustomerID = 1

Option 2:
Table - Sales Order:
SalesOrderID = 1
CustomerName = Microsoft

Options 1 Pros: when you change something in customer table it will reflect on all your data. If you use Search Query that uses Like statement this require more overhead as you have to link it back to customer to get the actual value. Require additional table to look up the actual value.

Options 1 Cons: Data doesn't make since when you look at the table. You may not want all data to change based on the new change meaning old entries that may not apply anymore. if someone delete a customer entry all your data connected to that line goes blank.

Options 2 Pros: you can make sense of the data by looking at the table. if someone delete an entry in customer table you don't lose any information. don't need query lookup query. No additional query need with Like statement

Options 2 Cons: may be bigger in size wise, a change in customer table will not reflect past data

Hope this make it more clearer

 

[gemma-the-husky]

in fact

option 1 - all pros, no cons
option 2 - no pros, only cons

and i would NEVER actually define a field in a table as a combobox/lookup or whatever MS offers - cause that gives you even worse issues when you look at the data - you cant see what is ACTUALLY stored in the field


i take your point about data in the tables, but the point is, no one should ever EVER look at the tables -(the db manager might need to, to investigate anomalies, is all)

the thing is, users should interact via forms, queries etc , and you link the numeric value of the customerid, to the text value by using queries.