Mar 8 2007, 09:02 AM
I am trying to figuire out the best way to save memory.
If an entry on a table like [name] is used multiple times i should create a new table Name with an autonumber as the PK and use a combo box on a form.
my question is if there are 1000 different names is it still better to create the extra table?
I have found that if I do this, my forms require an entry be made, but is there a way to leave it blank? and not get an error?
Mar 8 2007, 10:19 AM
The main principle behind normalization is to reduce and eliminate reduntant data. If you have, for example, an Employee table and an Employee History table, you do NOT want to repeat the employee name with each history record. Doesn't matter how many employees you have.
So the question then becomes why would you create an employee history record if there was no employee?
Mar 8 2007, 11:44 AM
Okay maybe I should be a little more specific.
If I have a field named [Markup] and products can only be marked up between 35-100% I should just make a table with values ranging between 35 and 100 because multiple products can receive the same markup value.
So when I make a form and place the Combobox on it I am getting an error where it says the field needs a value.
What if it doesnt always have a value and I want to leave it blank?
Mar 8 2007, 01:13 PM
No, That would not be considered repeating data. In that case I would just use validation to restrict the amount. You gain nothing by using a lookup table there.
Mar 9 2007, 08:14 AM
OK so ...
I should make a table if-
the data is associated with multiple tables or business processes
I should use validation rules if-
the data is only repeated in a record set.
That helps me alot!!
what about tables that have a 1 to 1 relationship is it better to just compile them in one table or seperate them out
ie : 1 car can be picked up or delivered only 1 time, but I put the pick-up and delivery data in a seperate tables from the desciption of the car
That does not cause any problems does it?
Mar 9 2007, 08:32 AM
I would use a 1 to 1 table if not all the records in the parent have a child. So, in your example, If not every car gets picked up, then it makes sense to use a separate table.
Mar 10 2007, 04:30 PM
We avoid using 1-1 relationships just because some of the records have blank information.
For example, if most of your cars will be picked up, and there isn't much pickup information, I would create just one table. The pickup information will be Null in some of the records, but that really doesn't cause a problem. Practically speaking, the Null values are a lot easier to deal with in the application than managing the 1-1 records.
Of course if MOST of the cars will never be picked up, and if the pickup information is extensive (like dozens of fields), then the 1-1 tables might be worth it.
Another reason to use 1-1 tables is if they have different permission requirements.
Mar 12 2007, 09:12 AM
Great thats exactly what i wanted to hear!
Thank You Very Much
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here