By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,501 Members | 831 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,501 IT Pros & Developers. It's quick & easy.

Database size optimization?

P: n/a
I have a database in which I have 2 tables.
One table is Employees, other is Events.

I have only one field in Employees and it is EmployeeName (text 50 chars).
Events has fields AutoNumber, Start Date/Time, End Date/Time, EmployeeName.
I have relationships EmployeeName from Employees 1 to many for Events.

My question is:

Does every record in Events table take space for EmployeeName or is it coded
in database in another way so it doesnt take up size of EmployeeName?

How would I have to organize my database so it would use the least space per
record?

Thanx
Nov 12 '05 #1
Share this Question
Share on Google+
14 Replies


P: n/a
Mickey previously wrote:
I have a database in which I have 2 tables.
One table is Employees, other is Events.

I have only one field in Employees and it is EmployeeName (text 50
chars).
Events has fields AutoNumber, Start Date/Time, End Date/Time,
EmployeeName.
I have relationships EmployeeName from Employees 1 to many for Events.

My question is:

Does every record in Events table take space for EmployeeName or is it
coded
in database in another way so it doesnt take up size of EmployeeName?
The name will be held in every record.

You could hold an employeeid instead but it would mean modifying the
Employees table to add an id field and then doing a couple of updates on
the Events table to change to an id based field.

How would I have to organize my database so it would use the least
space per record?


Space doesn't matter unless you have more than say, 100,000 employees on
file.

However it would be better to change the Events table to contain an id
rather than a name. People's names do change and an id field would enable
the name to be changed and shown everywhere in the database.

For the Employees table you need to add an Autonumber field as a primary
key field.
So you would have:
EmpID Autonumber (PK)
EmployeeName Text 50

To change the Events table you first need to create a temporary table
tempEvents which contains both the EmployeeName and the Empid.

Create a maketable query which uses both current tables, linked on
EmployeeName. Add all fields from the Events table and the Empid field
from the Employees table.
Run this query and check some sample values from the table to make sure
that the correct ID has been associated with each name.

Rename the old events table as OldEvents.
Rename tempEvents to Events.
Delete the Employee name field from the new Events table.
(You might have to delete and re-create relationships between the two
table first - now based on EmpId)

To show a list of events without names then you just look at the Events
table.
To show a list of events WITH names then you must create a query based on
both tables, including the name from the Employees table.

Post back here with any issues arising.

Regards

Peter Russell



Nov 12 '05 #2

P: n/a
Thanks on comments.
But, if size is not an issue but number of record is then I will rather
keep the names because they enable easy control over events,
you don't have to decode EmployeeID if you have to change
something manualy.

Best regards, Mickey

"Peter Russell" <ru***@127.0.0.1> wrote in message
news:me**********************@russellscott.btinter net.com...
Mickey previously wrote:
I have a database in which I have 2 tables.
One table is Employees, other is Events.

I have only one field in Employees and it is EmployeeName (text 50
chars).
Events has fields AutoNumber, Start Date/Time, End Date/Time,
EmployeeName.
I have relationships EmployeeName from Employees 1 to many for Events.

My question is:

Does every record in Events table take space for EmployeeName or is it
coded
in database in another way so it doesnt take up size of EmployeeName?


The name will be held in every record.

You could hold an employeeid instead but it would mean modifying the
Employees table to add an id field and then doing a couple of updates on
the Events table to change to an id based field.

How would I have to organize my database so it would use the least
space per record?


Space doesn't matter unless you have more than say, 100,000 employees on
file.

However it would be better to change the Events table to contain an id
rather than a name. People's names do change and an id field would enable
the name to be changed and shown everywhere in the database.

For the Employees table you need to add an Autonumber field as a primary
key field.
So you would have:
EmpID Autonumber (PK)
EmployeeName Text 50

To change the Events table you first need to create a temporary table
tempEvents which contains both the EmployeeName and the Empid.

Create a maketable query which uses both current tables, linked on
EmployeeName. Add all fields from the Events table and the Empid field
from the Employees table.
Run this query and check some sample values from the table to make sure
that the correct ID has been associated with each name.

Rename the old events table as OldEvents.
Rename tempEvents to Events.
Delete the Employee name field from the new Events table.
(You might have to delete and re-create relationships between the two
table first - now based on EmpId)

To show a list of events without names then you just look at the Events
table.
To show a list of events WITH names then you must create a query based on
both tables, including the name from the Employees table.

Post back here with any issues arising.

Regards

Peter Russell




Nov 12 '05 #3

P: n/a
tblEmployee
employeeId autonumber (pk)
employeeName text(50)

tblEvents
eventId autonumber (pk)
employeeId long number (fk)
startDate dateTime
endDate dateTime

"Mickey" <mi****@mickey.com> wrote in message news:<bv**********@ls219.htnet.hr>...
I have a database in which I have 2 tables.
One table is Employees, other is Events.

I have only one field in Employees and it is EmployeeName (text 50 chars).
Events has fields AutoNumber, Start Date/Time, End Date/Time, EmployeeName.
I have relationships EmployeeName from Employees 1 to many for Events.

My question is:

Does every record in Events table take space for EmployeeName or is it coded
in database in another way so it doesnt take up size of EmployeeName?

How would I have to organize my database so it would use the least space per
record?

Thanx

Nov 12 '05 #4

P: n/a
What happens when you get two employees with the same name? It happens a
lot, and that is one of the reasons that name is not considered a good
unique id value.

Larry Linson
Microsoft Access MVP

"Mickey" <mi****@mickey.com> wrote in message
news:bv**********@ls219.htnet.hr...
Thanks on comments.
But, if size is not an issue but number of record is then I will rather
keep the names because they enable easy control over events,
you don't have to decode EmployeeID if you have to change
something manualy.

Best regards, Mickey

"Peter Russell" <ru***@127.0.0.1> wrote in message
news:me**********************@russellscott.btinter net.com...
Mickey previously wrote:
I have a database in which I have 2 tables.
One table is Employees, other is Events.

I have only one field in Employees and it is EmployeeName (text 50
chars).
Events has fields AutoNumber, Start Date/Time, End Date/Time,
EmployeeName.
I have relationships EmployeeName from Employees 1 to many for Events.

My question is:

Does every record in Events table take space for EmployeeName or is it
coded
in database in another way so it doesnt take up size of EmployeeName?


The name will be held in every record.

You could hold an employeeid instead but it would mean modifying the
Employees table to add an id field and then doing a couple of updates on
the Events table to change to an id based field.

How would I have to organize my database so it would use the least
space per record?


Space doesn't matter unless you have more than say, 100,000 employees on
file.

However it would be better to change the Events table to contain an id
rather than a name. People's names do change and an id field would enable the name to be changed and shown everywhere in the database.

For the Employees table you need to add an Autonumber field as a primary
key field.
So you would have:
EmpID Autonumber (PK)
EmployeeName Text 50

To change the Events table you first need to create a temporary table
tempEvents which contains both the EmployeeName and the Empid.

Create a maketable query which uses both current tables, linked on
EmployeeName. Add all fields from the Events table and the Empid field
from the Employees table.
Run this query and check some sample values from the table to make sure
that the correct ID has been associated with each name.

Rename the old events table as OldEvents.
Rename tempEvents to Events.
Delete the Employee name field from the new Events table.
(You might have to delete and re-create relationships between the two
table first - now based on EmpId)

To show a list of events without names then you just look at the Events
table.
To show a list of events WITH names then you must create a query based on both tables, including the name from the Employees table.

Post back here with any issues arising.

Regards

Peter Russell





Nov 12 '05 #5

P: n/a
"Mickey" <mi****@mickey.com> wrote in message news:<bv**********@ls219.htnet.hr>...
Thanks on comments.
But, if size is not an issue but number of record is then I will rather
keep the names because they enable easy control over events,
you don't have to decode EmployeeID if you have to change
something manualy.

Best regards, Mickey


I agree with Peter. If you make Employee a combobox on your form,
then you can show the Employee's name instead of his ID. Properly
normalized, efficient... what's not to like?
Nov 12 '05 #6

P: n/a
rkc

"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...
"Mickey" <mi****@mickey.com> wrote in message

news:<bv**********@ls219.htnet.hr>...
Thanks on comments.
But, if size is not an issue but number of record is then I will rather
keep the names because they enable easy control over events,
you don't have to decode EmployeeID if you have to change
something manualy.

Best regards, Mickey


I agree with Peter. If you make Employee a combobox on your form,
then you can show the Employee's name instead of his ID. Properly
normalized, efficient... what's not to like?


Normalization is not the issue.
What can be more normalized than a table with one field?


Nov 12 '05 #7

P: n/a
> What happens when you get two employees with the same name? It happens a
lot, and that is one of the reasons that name is not considered a good
unique id value.


Well thing is I have made a Delphi application in which in ComboBox I select
employee name, and then write log to database.
Thing is the name can't be the same as you have to know for which name to
make
a record, so if you had EmployeeID you would also have to be able to tell
which
one you want to select so you would have to give the same named employees
index number or something like that to be able to to select the propper one.
So you could write Mark Maey, Mark Maey 1, Mark Maey 2 or something like
that.
Nov 12 '05 #8

P: n/a
"Roger" <le*********@natpro.com> wrote in message
news:8c**************************@posting.google.c om...
tblEmployee
employeeId autonumber (pk)
employeeName text(50)

tblEvents
eventId autonumber (pk)
employeeId long number (fk)
startDate dateTime
endDate dateTime


Well that is the alternative what is wrong with:

tblEmployee
EmployeeName text(50)

tblEvents
EventId autonumber (pk)
StartDate Date/Time
EndDate Date/Time
EmployeeName long number (fk)

Nov 12 '05 #9

P: n/a
> I agree with Peter. If you make Employee a combobox on your form,
then you can show the Employee's name instead of his ID. Properly
normalized, efficient... what's not to like?


I'm not using form for data entry but my own application I made.
Nov 12 '05 #10

P: n/a
rkc

"rkc" <rk*@yabba.dabba.do.rochester.rr.nope> wrote in message
news:vw****************@twister.nyroc.rr.com...

"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...
"Mickey" <mi****@mickey.com> wrote in message

news:<bv**********@ls219.htnet.hr>...
Thanks on comments.
But, if size is not an issue but number of record is then I will rather keep the names because they enable easy control over events,
you don't have to decode EmployeeID if you have to change
something manualy.

Best regards, Mickey


I agree with Peter. If you make Employee a combobox on your form,
then you can show the Employee's name instead of his ID. Properly
normalized, efficient... what's not to like?


Normalization is not the issue.
What can be more normalized than a table with one field?


Huh... never mind.

His intention to include data like John Smith 1, John Smith 2, etc in the
Employee field is definately a normalization issue.

Perhaps the hangup here is that a Delphi combox is not multi-column.
Don't know. Never used Delphi.
Nov 12 '05 #11

P: n/a
If you used form for record entry you would also have to be able
to distinct employees with the same name so you would also
have to have some means to do that.

"rkc" <rk*@yabba.dabba.do.rochester.rr.nope> wrote in message
news:Vs*******************@twister.nyroc.rr.com...

"rkc" <rk*@yabba.dabba.do.rochester.rr.nope> wrote in message
news:vw****************@twister.nyroc.rr.com...

"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...
"Mickey" <mi****@mickey.com> wrote in message

news:<bv**********@ls219.htnet.hr>...
> Thanks on comments.
> But, if size is not an issue but number of record is then I will rather > keep the names because they enable easy control over events,
> you don't have to decode EmployeeID if you have to change
> something manualy.
>
> Best regards, Mickey

I agree with Peter. If you make Employee a combobox on your form,
then you can show the Employee's name instead of his ID. Properly
normalized, efficient... what's not to like?


Normalization is not the issue.
What can be more normalized than a table with one field?


Huh... never mind.

His intention to include data like John Smith 1, John Smith 2, etc in the
Employee field is definately a normalization issue.

Perhaps the hangup here is that a Delphi combox is not multi-column.
Don't know. Never used Delphi.

Nov 12 '05 #12

P: n/a
rkc

"Mickey" <mi****@mickey.com> wrote in message
news:bv**********@ls219.htnet.hr...
If you used form for record entry you would also have to be able
to distinct employees with the same name so you would also
have to have some means to do that.


How is John Smith 1 or John Smith 2 going to allow you select the correct
employee? How is the operator going to know the guy in the mail room is
John Smith 2?

Nov 12 '05 #13

P: n/a
> How is John Smith 1 or John Smith 2 going to allow you select the correct
employee? How is the operator going to know the guy in the mail room is
John Smith 2?


Probably more easily than if you have John Smith and John Smith.
Nov 12 '05 #14

P: n/a
rkc

"Mickey" <mi****@mickey.com> wrote in message
news:bv**********@ls219.htnet.hr...
How is John Smith 1 or John Smith 2 going to allow you select the correct employee? How is the operator going to know the guy in the mail room is
John Smith 2?


Probably more easily than if you have John Smith and John Smith.


If they do it will be by coincidence instead of design.

Nov 12 '05 #15

This discussion thread is closed

Replies have been disabled for this discussion.