Database questions

[mama16]

I need some help or suggestions
I have to build this database, I was thinking on using Access. The form will have about 12 fields. I'll collect customer information data and populate the database with it. A field is going to be storing Wave files, is that possible in Access?
Every month I'll have to upload the info in my DB into another Database. They are using MySQL. I have never used MySQL. If I create the Database using Access, can I do the uploads into the MySQL DB? Is that possible?
I already know the field name and Data type they are using. I'll just have to create the DB with the same Field Name and Data types. What about the script that'll do the upload? Is is complicated?
Any suggestions?

 

[HitechUser]

mama16,

Yes you can store WAV files in Access (create an OLE field). If you do not "clean up" the database, these WAV files may create "database bloat".

As far as MySQL, I am not a expert, but I believe it supports an ODBC connection. You can create some VB code (in Access) to "copy" your data to the MySQL database. Buuuuuuuuuuutttt (isn't there always a but) I am not sure the WAV field (your wave file) will correctly be transmitted using the ODBC connection. Access creates a "header" in the OLE field for each object stored. What I mean by object is spreadsheet, word document, JPG image, WAV file, etc. etc.

Soooo, happy Access coding and let us know if you have more questions.

I hope this helps..

Good Luck...

 

[mama16]

Just before I start building this DB I would love to get some feedbacks from all of you experts. This database will have
10 fields. These are,
Service Name, Text
Service Type, Drop Down Box
Highway #, Drop Down Box
Exit, Drop Down Box
Priority, Drop Down Box
Phone, Number
From Date
TO Date
Name OLE
Details OLE

It's going to contain two tables
Table A
Highway # PK

Table B

AutoNumber PK
Highway #
Service Name,
Service Type,
Exit,
Priority,
Phone,
From
TO
Name
Details

Does it look like it's Normalized? Highway # does not repeat so I made it an unique key.
I could also keep Highway# and Exit# on the same Table A and link it to Table B using the Highway #.
I'd appreciate any suggestions.

 

[mama16]

Please, any feedbacks?

 

[willir]

Hmmm, sorry I did not see this before.

Just before I start building this DB I would love to get some feedbacks from all of you experts. This database will have 10 fields. These are...

I think you mean your main form will have 10 fields.

The database includes the tables, forms, reports, etc.

Before moving on, what is the purpose of the database? It is easier to determine a database design if one knows the objective of what data is to be collected, and what information needs to be reported.

You seem to want to track service type for highways, in which case your design looks fair.

HitechUser is correct that you can use OLE to captrue your wave files, and I see that the "Name" and "Details" will be your OLE "fields" -- perhaps, almost like leaving a voice message John smith wants to be picked up on Hwy 100 at exit 300. Cool. Saves typing.

Here is one problem HitechUser indicated. Access is not really designed to store OLE files or images, etc. As I understand it, Access handles these files by storing them outside the "actual" database, and uses links within the database to find the information. The size of the Access database can grow very quickly to an incredible size, hence the term "bloat".

I would attempt to approach this problem a little differently. I would store my wave files outside the database, and use hyperlinks to the wave files. Although hyperlinks are also not real "fields" in Access, they use up considerable less space. But automating this "process" where a person calls and leaves their name and location would certainly be a challenge.

Richard

 

[mama16]

Thanks a lot Richard.
How can I store the .wav outside the actual database. Sorry, but I'm a little confused. I created the table with the fields "Name" and "Details" and made them OLE type. Also, I added more tables than what I had before. Please, let me know what you think.

Table A
Highway # PK

Link Table A Highway # to Table B Highway # as One to Many

Table B
Autonumber PK
Highway #
Exit #

Link Table B Autonumber to Table C Autonumber as One to one-- "I'm not sure about this relationship"""

Table C

Autonumber PK
Service Name, Text
Service Type, Drop "Retaurant, Gas Station etc.."

Highway #, Drop Down Box "All Hwy in a state
Exit, Drop Down Box All Exits for that Hwy
Priority, Drop Down Box Numbers 1 through 6
Phone, Number
From Date starting date
TO Date expiring date
Name OLE .wav
Details OLE .wav


Table D

Priority PK

Link this table to Table C Priority as a one to many
This field is going to hold numbers from 1 through 6


Table E

Service Type PK

Link this table to Table C Service Type as one to many

The reason I added the service type and the priority table is because the information in those tables is static, it's not going to change.

Any suggestions?

 

[willir]

Well, lets go back to the basics. What are you trying to accomplish? What is the objective of the database? What type of information are you trying to gleam?

And with the wave files, it just may be easier to store them internally within the Access database. However, if I were to create an HR database that included photos of employees, I would store the images as jpeg files and link to the photo within Access using the hyper-link field type (it is an option when you define the field).

From my perspective, the problem with storing a wave file is...
- how to capture the sound, if indeed you are going to have people speak the information. Regardless of whether the files will be stored within the Access database, or outside of Access, this will may be a challenge. Capturing sound in a manual fashion is not hard, but to automate the process would certainly keep me busy for a bit. Regardless, your idea is very novell, and certainly interesting.
- you will need to ensure the correct "plug-in", such as windows media player, kicks in to play the sound file.

 

[mama16]

Thanks willir!

I modified my tables a little more and I think this make sense now.
The answer to your question,
I'm selling a service to some customers. These are gas stations, Restaurants etc. Detailed information about merchants, located by Interstate exit will be on a database. This database will have all the Highways for a particular state and all the exits. Example, I-95 contains this many exits, I-40 and all its exits etc.
The priority field , will have numbers from one through six. A number will be assigned to the merchants depending on how close they are to the exit. The closer the merchant is to the exit, the lower the number the merchant gets, in this case 1, but the higher the price the merchant pays. The farther the merchant is to the exit, the higher the number the merchant gets, in this case 6, but the lower the price the merchant pays.

The .wav file,
One .wav file contains the name of the business and the other .wav file contains the business’s detailed information

Below are the changes I made to the design,

Table A
Highway # PK

Link Table A Highway # to Table B Highway # as One to Many

Table B
Exit_Seq (Autonumber) PK
Highway #
Exit #

Link Table B Exit_Seq to Table C Exit_Seq as One to many

Table C

Autonumber PK
Exit_Seq, number
Service Name, Text
Service Type, Drop "Retaurant, Gas Station etc.."

Highway #, Drop Down Box "All Hwy in a state
Exit, Drop Down Box All Exits for that Hwy
Priority, Drop Down Box Numbers 1 through 6
Phone, Number
From Date starting date
TO Date expiring date
Name OLE .wav
Details OLE .wav


Table D

Priority PK

Link this table to Table C Priority as a one to many
This field is going to hold numbers from 1 through 6


Table E

Service Type PK

Link this table to Table C Service Type as one to many

Any suggestions?

 

[mama16]

I forgot to ask you about your comment

"And with the wave files, it just may be easier to store them internally within the Access database. However, if I were to create an HR database that included photos of employees, I would store the images as jpeg files and link to the photo within Access using the hyper-link field type (it is an option when you define the field)."

Do you think its better if I store the .wav files on the network and link them within Access using the hyper-link field type?

Thanks