Database Design

[Dyers]

I think you could truly benefit by completing an introductory tutorial book before tackling db design.

Agreed, but here's what gave me the audacity to give it a go:

From here, if you want to migrate to Access, start building database. Will be happy to review it.

[June7]

Maybe I jumped to conclusion you already have a good working knowledge of relational database principles, basic Access functionality, and programming concepts. Later posts are not supporting that assumption.

[Dyers]

Maybe I jumped to conclusion you already have a good working knowledge of relational database principles, basic Access functionality, and programming concepts. Later posts are not supporting that assumption.

Oh? My opening remarks to this topic included this;

Several weeks ago, for the second time, I saw that our data was getting away from us, and attempted to build a db. I made an attempt to do this with OpenOffice by myself, but quickly realized how little I know as compared to what I needed to know to do any justice to the db.

I too made a baseless assumption; I assumed that I'd be treated respectfully by a help forum moderator.

[June7]

Not meaning to be disrespectful. I read a lot of threads and was already disconnected from the thread with your original comments. This one started out giving me the impression you were already familiar with the necessary tools and I was not redirected by the quote in post, should have reread it more carefully.

Was just suggesting that maybe you should step back a bit and do gain the familiarity needed. Was any of the other info in post 15 meaningful?

[ssanfu]

I too thought you knew more about databases. Remember, writing loses the nuances of the spoken word; it is easy to be misunderstood.

OK, so I looked at your dB and made some changes. You had two tables linked on two different field types. Can't do this - the linked field MUST be the same type.
I changed table names - having table names and form names the same is confusing. Should come up with a naming convention.

Speaking of relationships (linking), here is some reading for you on autonumbers/relationships/primary keys:
Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers

Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

Use Autonumbers properly
http://access.mvps.org/access/general/gen0025.htm

And here is a link to a tutorial. It get recommended a lot:
http://www.rogersaccesslibrary.com/forum/forum46.html

Work through the tutorials.....

Also see: The Ten Commandments of Access https://www.accessforums.net/databas...tml#post252662


I have a very general idea of what the dB is for, but I'm still not sure of what the relationships are and where you are headed. Would you describe the dB as if you are in line at the store and you are explaining to someone that doesn't know your business (like me ).

Look at the forms (2) that end with "_SS". The "Dyecolors" form has a subform "Formulas". Select a color in the details section and the subform displays the powders and weights to create the color..... (right?).

[Dyers]

Hi, June & Steve;

Thanks for your messages. I think I'll be able to spend some time with them later this evening. It looks like there's a lot here to start to digest.

Dave

[Dyers]

Was any of the other info in post 15 meaningful?

It was...to varying degrees, of course, but see below.

A form can be bound to table then filter/sort can be done by using the intrinsic tools from the menu bar or right click menu or by code to set properties of form.

I wouldn't have been able to find the words, but I think the menu bar tools are how I've sorted db records in the past. My experience with databases was strictly with flat files, and MS Works was the tool I most often used.

All of the forms are set for Datasheet view. You probably should switch to Single or Continuous View unless the form is intended to be a subform and even then Continuous view is more versatile. A form in Datasheet view will not show a subform, have to click the + in the left margin to expand the subform. Either way, need to set the Master/Child Links properties of the subform container control.

I remember the first and only car I ever had with electric windows; for a very long time, I'd reach for the crank when I wanted to open or close mine. Relational databses are sufficiently new to me that I don't even have a good idea of what they can do, but I also recognize my tendency to reach for the bits that are familiar, such as the datasheet.

The value in Image URL field is not a URL, it is just a file name. Do you want to be able to display these png images on form or report? Are these images stored in a folder on your computer? Dynamic display of images would be easy in Access 2007/2010/2013. Earlier versions require code. https://support.microsoft.com/kb/148463?wa=wsignin1.0

The gentleman who helped me with the OOo db named this field. It worked, as I understand, because of an option in that field's properties that required no more of a URL than file name if the image files were in the same directory as the db. I didn't ask, and we didn't talk about it, but my suspicion for his having named the field with URL rather than Name is that he foresaw the need to put them in a separate directory when a proper URL would be required. In that it functioned the way as it was, it didn't get much of my attention.

So yes, currently they are in the same directory as the db. I think I would prefer that they were in a subdirectory of their own, but this works. The images are 50px sq scans of the samples we use when evaluating a new color formula, and they don't exist anywhere else on my HD. Resolution is terrible, but color is quite accurate, and that's what I'll rely on the images for; color. I can't answer your question about the inclusion of the image(s) in a report because I have only limited theoretical knowledge of a db report's function, and no experiential knowledge. Currently, it is used in the Dye Colors form, and it's very helpful in that location. In that form, I have the visual cue for color, plus the formula for that color which allows me to infer certain characteristics in a color that my be either desirable or not at the moment.

Advise not to use spaces or special characters/punctuation (underscore is exception) in naming convention.

Thanks; I think I must have known that, but when I edited the .csv files before importing them into Access (each one represents a table, and was generated by OOo when I needed to convert that db to Access), it slipped my mind. When things seemed to work as expected, I just kept going.

Why is the textbox bound to a field (Dye Color ID) that doesn't exist in the form's RecordSource?

I'm not sure what that means, but if something's missing, it's most likely due to the inexperience of the guy who converted the tables from .odb to .csv to .mdb format.

[Dyers]

Uh-oh...I came to double-check my notification settings, and found that my response to Steve's message wasn't here, and I can only imagine I didn't complete the post somehow. Fortunately, Auto-Save still had it.

I too thought you knew more about databases. Remember, writing loses the nuances of the spoken word; it is easy to be misunderstood.

Very true.

OK, so I looked at your dB and made some changes.

Thank you very much, that's a lot of work you've put into this. I haven't spent much time with it yet, but I wanted to answer your question about the functions of the database.

You had two tables linked on two different field types. Can't do this - the linked field MUST be the same type.
I changed table names - having table names and form names the same is confusing. Should come up with a naming convention.

I wanted to rename fields and tables for the sake of clarity and accuracy, but I tried to maintain his convention. Any breach would have been mine. I'm fine with your changes. I appreciate it.

Speaking of relationships (linking), here is some reading for you on autonumbers/relationships/primary keys: <snip>

Work through the tutorials.....

This is going to come slowly for me; I'm not the fastest learner, anyway.

I have a very general idea of what the dB is for, but I'm still not sure of what the relationships are and where you are headed. Would you describe the dB as if you are in line at the store and you are explaining to someone that doesn't know your business (like me ).

As I understand, the two primary relationships are both many-to-many.

1. We stock roughly 22 commercial dyes called Dye Powders in the db. The first primary relationship I see is between these commercially prepared colors, and our Dye Colors whose formulas are comprised only of these powdered acid dyes. While we'll add to the list of dry powders, it will remain short. Any one of these commercially prepared dyes can and is used in many of our dye colors. Another way to put that is that our dye colors are based on formulas that we've developed, and are based completely on the list in the Dry Powders table. As I understand, the associated db form is to facilitate edits to that table.

2. We stock hundreds of our dye colors, or more precisely, their stock solutions. The second primary relationship I see is between these dye colors, and our product, variegated yarns. These variegated yarns are more commonly called hand painted yarns, and the infinite combinations of colors are most commonly called colorways. A colorway can be made of many dye colors, and individual dye colors can be and often are used in many colorways.

When we develop a new color, we often approach it from a number of angles. In theory, purple can be made from red & blue, but in practice, magenta and turquoise makes much nicer ones, for instance. Our stock solutions are made with a total of 15g. of dye dissolved in 500 ml of water, and for the sake of accuracy, we don't mix smaller batches for our samples. Consequently, we undoubtedly have a number of dye colors we've never used in any colorway. Because space is finite, we will probably want to reclaim space for new colors. It would be very helpful to know how many colorways use a particular dye color.

Both my son & I are likely to apply the next layer of a colorway. A layer, similar to it's use in graphics softwares, is the translucent application of a single dye color in a specific way to the fiber. The order of applications of dyes seems to matter, but less than might seem obvious at first. Still, we maintain those records as though it always mattered as much as it does only part of the time. Currently, the colorway notes can be written using a number of shorthand-like annotations that we both make, but is not universally understood by the other. A list box field for data entry into tblLayers that will allow either of us to use the same text with just a click is a perfect solution to partially legible notes jotted on any sort of paper, and stored in a jobber clipboard. The idea also is that either of us can apply the next layer, and not loose either our place in the process, nor any momentum. This requires a trick I haven't come to understand yet...perhaps a check box field in the correct form (Colorways?). If a production log is used, the additional data it could provide regarding when a colorway was last made, how many times total, how many skeins total, etc. could be very useful.

Other questions I'd like for the db to be able to answer include locating any formula that uses one of the commercial dyes that may no longer be manufactured, or have changed, preventing a formula from accidental redundant development, and possibly to learn about exploitable characteristics of the commercial dyes we use.

The db will be stored on my computer which is also the file server; backup will be via my data partition to the cloud. It will be accessed from my son's machine, as well as from the old notebook that resides in the studio. Data entry should be possible from any device that resides on or can be accessed from outside the network including my son's smart phone. Hardware security via the router, software security via a firewall.

Look at the forms (2) that end with "_SS". The "Dyecolors" form has a subform "Formulas". Select a color in the details section and the subform displays the powders and weights to create the color..... (right?).

Yes, you've got it. It's a very clean, easy to read & understand interface, too. I was imagining that I'd also use this form for data entry; is that your take, too? I'll begin some of this reading later in the day.

Thanks for your interest, and willingness to steer, Steve.