I am very new to the Alpha platform but have been using Access/.NET for many years. I need advice on the record id for each table created in Alpha.
I have always created the ID field as a numerical, starting at 1 and autonumber (autoincrement). But, most of what i read here says use a character format and then set the starting value at something like 000001. What is best, keeping in mind SQL server down the road.
I also use a universal lookup table consisting of 3 fields. ID, tblName and Value.
When used in a lookup field, i filter the lookup by the value in the tblName. Example: if i use it in the "Agency Name" field the lookup values are filtered by the tblName value "tlkpAgency".
The value stored is the ID value of the lookup table. If i store the text value as opposed to the id value, if the value changes later then the previous data entered needs to be updated, such as the personnel table when someone changes their name due to marriage.
Using a universal lookup table is very easy to maintain and find as opposed to numerous lookup tables. BUT, this is the way i have done it in the past.
So, my main question is; do i use a numeric value or a character value as each records id. And, since the universal lookup table id will be numerical, each field in every table that uses it will also need to be numerical. if i use numerical, does the width value matter. if i set it at 6, what happens when it hits one million? There is no width setting in Access for the autonumber so this is new to me.
since changing any of this later would be a logistical nightmare, i want to make sure i do it right the first time.
I have always created the ID field as a numerical, starting at 1 and autonumber (autoincrement). But, most of what i read here says use a character format and then set the starting value at something like 000001. What is best, keeping in mind SQL server down the road.
I also use a universal lookup table consisting of 3 fields. ID, tblName and Value.
When used in a lookup field, i filter the lookup by the value in the tblName. Example: if i use it in the "Agency Name" field the lookup values are filtered by the tblName value "tlkpAgency".
The value stored is the ID value of the lookup table. If i store the text value as opposed to the id value, if the value changes later then the previous data entered needs to be updated, such as the personnel table when someone changes their name due to marriage.
Using a universal lookup table is very easy to maintain and find as opposed to numerous lookup tables. BUT, this is the way i have done it in the past.
So, my main question is; do i use a numeric value or a character value as each records id. And, since the universal lookup table id will be numerical, each field in every table that uses it will also need to be numerical. if i use numerical, does the width value matter. if i set it at 6, what happens when it hits one million? There is no width setting in Access for the autonumber so this is new to me.
since changing any of this later would be a logistical nightmare, i want to make sure i do it right the first time.
Comment