Normalization - Qs about junction tables and booleans. (1 Viewer)

jjatwork

Registered User.
Local time
Today, 22:23
Joined
May 6, 2019
Messages
17
I've done a lot of reading on normalization, but I'm having a hard time grasping some of the concepts.
Especially how to use foreign keys and junction tables.

Let's say I have a DB where I keep track of bicycles. I have a table, 'tblBikeInfo' with a lot of fields containing info about each specific bike, which I'm trying to separate into multiple tables.

As it is, I have a string field named 'BikeType' in my table and I'm restricting the textbox input to 'Male', 'Female' or 'Unisex'. If I normalize the DB, I'll end up with 1 or 2 extra tables. Will that not demand more resources from the server?
I don't mind putting in the work to do it right, I just want to be sure which path is the correct one before starting.

1. Properties with n:1 relations, like brand, size or, ie., color.
Is it best practice to make another table with colorID and colorName, keep the field in 'tblBikeInfo'and have the colorID as a FK, or would you remove the field from 'tblBikeInfo' and make a junction table, containing the bikeID and the colorID

Can anyone provide a brief overview of the differences between the two methods and when to use which one?


2. What about booleans, like a checkbox indicating if the bike is electric or not.
Is there any need for separating such properties into separate tables?


Thank you all in advance for any advice, tips and/or helpful comments.
JJ
 

Minty

AWF VIP
Local time
Today, 21:23
Joined
Jul 26, 2013
Messages
10,355
I'm assuming any bike can be many colours. Therefore to store the available colours for any given bike you would need a junction table storing the BikeID and the ColourID as Foreign keys. I would make them a unique combined PK so you can't list the same bike and colour twice by mistake.

However a brand is a fixed single entity for that BikeID's manufacturer, so you would have a Manufactures table, and store the Manufacturers ID in the Bike table as a Foreign key.

Multple normalised tables don't cause any significant overhead and generally simplify writing queries and reports.

Some people argue against tables with ID's for simple lookup values (Male/ Female is an obvious one).
However your Bike types may get additions (unicycle for instance) and it's much easier to add a record to the table, than to find all uses of the combo and add it manually to the list items.

I prefer storing an ID over the unique text value, it's cheaper resource wise, as if you mistype something correcting the source data corrects all the underlying displayed values automatically.

Does that help ?
 
Last edited:

jjatwork

Registered User.
Local time
Today, 22:23
Joined
May 6, 2019
Messages
17
It certainly helps, thank you.

So, if I understand correct, in case of a n:1 relations, I would keep the field in the 'primary' table to hold a FK, whereas in the case of n:n I would make a junction table.
Or am I trying to oversimplify it?

I'm not sure what a lookup value is, but the reason you mention is exactly why I was leaning towards separating things like 'type' into another table. I'm thinking: 'If any addition to the choices is ever possible, it needs a separate table'.

Still not sure about booleans, though. It will always be true or false and can never have additional choices.
Is it ok to store such a property in my 'main' table?

Tyvm for you time and advice. It is very much appreciated.
JJ
 

Minty

AWF VIP
Local time
Today, 21:23
Joined
Jul 26, 2013
Messages
10,355
So, if I understand correct, in case of a n:1 relations, I would keep the field in the 'primary' table to hold a FK, whereas in the case of n:n I would make a junction table.
Or am I trying to oversimplify it?

Nope you are spot on with the above.
I'm not sure what a lookup value is, but the reason you mention is exactly why I was leaning towards separating things like 'type' into another table. I'm thinking: 'If any addition to the choices is ever possible, it needs a separate table

Again pretty good description of normalisation. The word commonly used is an Entity. and Entity type. Rather than having 3 fields called Male,Female & Unisex, as boolean tick boxes (Spreadsheet stylee), you correctly have a type and store that instead. When Unicycle arrives or Tandem, you don't have to redesign your whole database.

A lookup value is a table of related information that you use to control data entry. E.g. tblManufacturers
ManuID - PK Autonumber
Manufacturer - Text
other fields

You would use this whenever you wanted to store a Manufacturer in a related table e.g. tblBikes. This is to stop people typing in Eddie Merks, then Eddy Mearks instead of Eddy Merckx

You want consistent data, now you could just store the Manufacturer as a text field in the tblBikes but you really should store the ManuID as it's more efficient, and scales better.

With Booleans - simply store them as a Boolean field in the table. Yes/No True/False. definitely don't use a table for those. Some developers avoid booleans, and use a integer or byte field instead, I'll let you google research the arguments about that.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 08:23
Joined
Jan 20, 2009
Messages
12,849
You might not store Electric as a Boolean. Instead consider a Power field to record the Watts of the motor. Any value means it is electrics while Null means no motor.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:23
Joined
Sep 12, 2006
Messages
15,614
It's a bit tricky.


Let's say you a bike called a "Chris Boardman Special" that come in mulitple sizes, and a range of colours. How do you need to use your data. Do you want you user to

a) pick the bike, THEN
b) pick the size, THEN
c) pick a colour

or navigate to the bike in a different way.

If you have 3 of exactly the same bike, can you distinguish them. Do you have a serial number?

You need to consider all the atributes you might want, and then structure the data tables accordingly. Maybe you have a M boolean AND a F boolean, and you get Unisex by choosing bikes with both M and F set to true.

You don't get more tables because you have more variable attributes. You just have one bike table, but you filter the bikes based on the atrtributes you select.

It might be worth looking at the way on line Bike sites work, or maybe Car Sales sites work to see how they let you navigate to the model you want. Travel Agents are also good- how they let you drill down to the holiday you want. It's all the same really.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 14:23
Joined
Oct 29, 2018
Messages
21,358
Hi. Perhaps I’m way off here but in most cases when the topics of boolean fields and normalization are discussed, it is usually about the issue of repeating groups. Meaning, if your table has multiple boolean fields, then it’s possible that they probably belong in a separate table.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:23
Joined
Feb 28, 2001
Messages
27,001
You said you have some confusion regarding use of foreign keys (FK) and junction tables.

The reason that normalization works so well is that there is a rule about how you place data in a given table. To have an FK, you first have to have a prime key (PK) in another table. Minty mentioned the concept of "entities" which is kind of generic sounding - because it has to be. Access and other databases tend to abstract the things (entities) they describe. So if your entity is a person, you would have name, birthday, gender, perhaps some physical descriptions like height & weight, and maybe a few other things.

With general databases, you never know ahead of time what tidbit of information you will need for some report or form, so how do you store all that info? By making a table dedicated to entities of that class - i.e. a person table. But now, you have records where that person must be identified. So you have two choices - the flat-file or spreadsheet way and the relational database way. In spreadsheets, you tend to repeat data. The relational way says to assign a unique PK to each record and make a copy of that PK (and ONLY the PK) in any table that needs to reference that person. In that referencing table, that copy becomes the FK and is the stand-in for any and all data about that entity (person). The FK becomes the pointer to where you find data about that person and allows you to design the correct database structures based on "knowing where to look" i.e. which table (the person table) and which record (indicated by the FK). The FK thus expresses the relation of the current record of interest to the person in question.

Crucial to this concept is "purity of entity." This is a rhetorical question: Where you do store data about a person? The answer is "It depends." In order to maintain purity of your table structure, there is a rule: Anything you store in a given table must be fully determined once you identify the prime key for that record. Anything that is NOT fully identified by the PK doesn't belong in the table. It belongs in some other table.

Case in point: Here is my record, sitting in a table somewhere and you want to ask two questions: What is my age and what car did I drive last year? You can compute my age from my birthday, so you go to the person table to look that up. But what about my car? Cars break down, or I get tired of them, or I get a wild hair somewhere. So which car I drove last year depends on my PK but it ALSO depends on my car ownership history which involves a DATE dependency. That second dependency means it cannot go in the person table because it violates the purity rule. It needs to be in another table that includes my person PK (technically, as an FK) and a couple of dates showing when I got the car and when I ditched it.

I'm going to break here and pick up with junction tables in another post.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:23
Joined
Feb 28, 2001
Messages
27,001
The other part of your question is about junction tables.

In Access you have three enforceable relationships: 1:1, 1:many, and many:1 - that's it. This leads to partner tables (1:1), child tables (1:many), or definitional tables (many:1). But the real world has a very common fourth relationship type, many:many, and it occurs a LOT.

The solution used by Access and most other DB packages that I know has to do with a middle-man table called a junction table. In logic theory, the junction table is used to enumerate or list the points of overlap between two other entities, each in their own tables. If you think of the overlapping circles of a Venn diagram, the individual tables are the circles and the junction table relates to the area where the two circles overlap.

Here's a case from my past when I was employed as a contractor for the US Navy. We had these "refresher courses" that we had to take each year to keep up our eligibility. Everybody had to take them and there were several to be taken. (Many/many!)

So to track that, the educational software had a person table that held every contractor using an identifier as PK. There was a course table that held every course on the required list (as well as some "optionals" for special-case certifications) using a course number as PK. The way to remember which contractor took which course was a junction table. In that table, you would have these values: My ID, the course ID, the mandatory completion date, and the date on which I completed the certification. There was also a "grade" field, which is important for illustrative purposes.

That junction established a pair of FKs - one to me, one to the course, and it also held some data that, for purity of entity reasons, didn't belong in either the person table or the course table. The date on which I took the course depended on my schedule and ability to take that course (which was on-line). It also depended on whether that course was required in a given year. Requirements changed from year to year as courses changed or Congress passed some new requirement or some admiral got a burr up his butt about what we needed to know.

It isn't always done this way because it depends on the nature of the requirement, but for this table, what they did was they kept the junction record of when I took the course but THEN at the beginning of the fiscal year, they did a sweep and loaded NEW junction records for each contractor and each required course, with an empty "taken" date and the last day of the new fiscal year as the due date. So the junction table was not only a requirements table but a history table as well.

Junction tables tend to have shorter records because they can refer to the joined entities through their FKs, but they CAN have data other than FKs. The example I gave was used for history as well as for requirements. It also recorded the score on the exam that was part of the course.

I mention the grade and "taken" date to show that junction tables have purity of entity as well. In this case, the entity is a bit abstract - an online session with a computer-aided learning course. But the entity showed things that were defined by the single record: student, course, due date, taken date, grade.

Interestingly enough, the "session" record might not have had a "true" PK, not that it matters. The purity of entity concept merely says that if there is a PK, then everything in the record must depend on it. If there is no PK, the record contents should still depend on what it would have been if you had one. And it is not uncommon for a junction table to NOT have individual PKs. OK, in the Navy case, a compound PK was possible - person ID, course ID, and Due Date. But there is no way for me to know if that was an actual compound PK or merely a viable candidate for selection as PK.

This entity record is ALSO subject to the "purity" concept because it is a table in a relational database. And because it has two FKs (one to each of two tables), it has two parent tables.

The nomenclature for relationships talks about parent/child entities. Another way of saying this is "dependent" or "independent" entities. A person is an independent entity. A particular course is an independent entity. The session where a person takes a course is a dependent entity. In relational terms, the independent entities have PKs whereas the dependent enties will have FKs to the things on which they depend. So the junction table represents a doubly-dependent entity.
 

jjatwork

Registered User.
Local time
Today, 22:23
Joined
May 6, 2019
Messages
17
Nope you are spot on with the above.


Again pretty good description of normalisation. The word commonly used is an Entity. and Entity type. Rather than having 3 fields called Male,Female & Unisex, as boolean tick boxes (Spreadsheet stylee), you correctly have a type and store that instead. When Unicycle arrives or Tandem, you don't have to redesign your whole database.

A lookup value is a table of related information that you use to control data entry. E.g. tblManufacturers
ManuID - PK Autonumber
Manufacturer - Text
other fields

You would use this whenever you wanted to store a Manufacturer in a related table e.g. tblBikes. This is to stop people typing in Eddie Merks, then Eddy Mearks instead of Eddy Merckx

You want consistent data, now you could just store the Manufacturer as a text field in the tblBikes but you really should store the ManuID as it's more efficient, and scales better.

With Booleans - simply store them as a Boolean field in the table. Yes/No True/False. definitely don't use a table for those. Some developers avoid booleans, and use a integer or byte field instead, I'll let you google research the arguments about that.


Thank you for the confirmation and the in-depth explanation. It certainly helps me understand the issue!

I've done some reading and now better understand what entities and lookup values are.
Just a quick question, though. In this case, the colour would still be an attribute of the entity (the bike), even though it is separated into its own table, correct?
Or would the 'ColourID' in theory be the entity in its own table and the 'ColourName' the attribute?

I'v been working under the assumption that using a meaningless PK comes with more benifits than not, so for most of my tables the PK is simply an auto-increment.

For now, I will keep booleans in the 'main' table, but I'll keep in mind that I need to do some thorough research on the subject.

Thank you again for taking the time.

You might not store Electric as a Boolean. Instead consider a Power field to record the Watts of the motor. Any value means it is electrics while Null means no motor.


Of course! I hadn't even thought about that.
I think I've read many times, that non-PK fields should not be inter-dependent, but sometimes it helps to get the same info in a different way.
Since I need to track which battery is installed on the bikes anyway, I guess I could apply your example to that entry. (Entity?)

Brilliant. Tyvm for your comment.

It's a bit tricky.


Let's say you a bike called a "Chris Boardman Special" that come in mulitple sizes, and a range of colours. How do you need to use your data. Do you want you user to

a) pick the bike, THEN
b) pick the size, THEN
c) pick a colour

or navigate to the bike in a different way.

If you have 3 of exactly the same bike, can you distinguish them. Do you have a serial number?

You need to consider all the atributes you might want, and then structure the data tables accordingly. Maybe you have a M boolean AND a F boolean, and you get Unisex by choosing bikes with both M and F set to true.

You don't get more tables because you have more variable attributes. You just have one bike table, but you filter the bikes based on the atrtributes you select.

It might be worth looking at the way on line Bike sites work, or maybe Car Sales sites work to see how they let you navigate to the model you want. Travel Agents are also good- how they let you drill down to the holiday you want. It's all the same really.

It's very tricky. At least for me. ;)

The DB is for internal use. Users will be keeping track of a number of bikes and the repairs being done on them. (Maybe I should add this info to my profile or signature? I want to be up front about seeking help in a real-case project, to secure steady employment, so some may not consider it non-profit.)

I'm not entirely sure how I built the lookup function, I'll have to double-check.
Some bikes share BikeID in the current system and physically, so I need to negotiate that issue.
Thankfully, the bikes have serial numbers and I've given each bike an internal DB_ID which is specific to each one.
Come to think of it, though, in some queries and functions I might actually have passed the BikeID as an argument instead of the DB_ID, which of course will result in some errors down the line.
I'll have to look at that.

Thank you for your input. Keeps the wheels spinning.

Hi. Perhaps I’m way off here but in most cases when the topics of boolean fields and normalization are discussed, it is usually about the issue of repeating groups. Meaning, if your table has multiple boolean fields, then it’s possible that they probably belong in a separate table.
As of right now, I have 3 fields containing booleans, indicating if the bike is electric, in-house and if payments are being made.
Going by Galaxiom's advice, I should be able to deduce if it's electric and/or in-house, by looking at related information, so some fields in the table was indeed redundant.

Thanks for your comment, any input is appreciated!


@ Doc

Thank you! I will have a read after dinner.
 

Minty

AWF VIP
Local time
Today, 21:23
Joined
Jul 26, 2013
Messages
10,355
If you ares simply storing repair details then a brand, type, serial number, colour would probably suffice.

Only record (e.g. have to enter) the data you need to achieve your task. Recording where the saddle height was set and what the customer had for breakfast probably don't actually do anything for you even if nice to have. Brevity can be a good idea.

If you are also selling then frame sizes, gear assembly, and a whole heap of other shizzle arrives.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:23
Joined
Sep 12, 2006
Messages
15,614
If its for a repair shop, I can't see the relevance of colour, personally - unless you need to paint it. Is it just for ID.


In this case, I would add whatever description you want to identify the bike. I doubt the bike name matters so much. Attach a physical label with a unique reference number, customer name maybe, and so on. Put this information in your bike table. Maybe take a few photos, and store these as jpegs attached to the bike ID.

You will know whats important. Maybe the chainset details/pedal types/brake info is more important than the bike itself.

It's a bit like a car repair shop, I imagine. You need a unqiue registration number to identify the motor, and a work sheet to decide what you are going to do. Then you need to record parts used, and labour hours. Something like that?
 

jjatwork

Registered User.
Local time
Today, 22:23
Joined
May 6, 2019
Messages
17
@ Doc

Thanks for the detailed walkthrough.

I've already separated Entities like Dept. and Team, which has lots of additional info like address, phonenumber etc. into their own tables, but I was unsure how to deal with things like colour and size, which will only have a few fields in their table.

I'll try to apply the 'purity of entity' concept and sort out any inter-dependencies.

I've been through relations a lot and have looked at those overlapping circles you speak of, but it simply does not compute for my brain. I'm sure it will sink in at some point.
I've seen the 'classes taken' example before, but again it's a case of being presented the same information in a different way. Your explanation made me realize a key component of junction tables: Sometimes they will contain data about the specific combination of FK's, which cannot be appropriately contained in any of the (parent?) tables they refer to. Is this correctly understood?

You lost me a bit at the end with the 'junction tables don't always have PK's'. I was under the impression that a juntion table always had a compound PK.
I think I have to start out as simple as possible and cross that river when I get there.

I really appreciate that you took the time for such a detailed explanation. Tyvm!



@ Minty and Dave

Nothing is being sold.
I'm not sure how much I'm allowed to disclose, but it's more like a servicecenter, which occasionally lends its bikes out of house, that has an inhouse repairshop.
So the DB is supposed to keep track of repairs made, which department, group and team the bike is currently designated to, buydate, milage, supplier and some other information for insurance/warranty purposes and some additional info for billing.

Dave. Yes, the colour is 'needed' to make it easy to visually identify the bike physically.
Your example with the car repair is about right. The thing is that I have to make 2 different 'systems' to keep track of the repairs. All repairs on inhouse bikes only need a brief note on what has been repaired, date and name of the mechanic, while all repairs on bikes designated to external users must be more detailed for billing purposes.

Maybe it's more complex than I anticipated at first, but I'll figure it out.

Thank you for both your comments.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:23
Joined
Feb 28, 2001
Messages
27,001
Sometimes they will contain data about the specific combination of FK's, which cannot be appropriately contained in any of the (parent?) tables they refer to. Is this correctly understood?

Correct. For example, in my training requirements table, the grade isn't purely an attribute of the person and isn't purely an attribute of the class. It is an attribute of whatever the junction represents, which in this case might be called a "session." This grade's existence depends on TWO tables - the person and the class - plus the due date in the junction record. So based on purity, the only place the grade CAN go is in the place that holds all the things it depends on.

Further note: Sometimes it is a real bear to try to figure out what the junction represents. Here, it is a session. But some junction tables are so abstract in nature as to defy immediate description. That is, however, not due to Access, but due to the abstract nature of the problem being analyzed.

You lost me a bit at the end with the 'junction tables don't always have PK's'. I was under the impression that a juntion table always had a compound PK.

Technically, NO table needs a PK if nothing depends on it. (I.e., more specifically, it is not a parent / has no child records.) Stated another way, if you have no need of an FK pointing into that table, you do not need a PK. However, depending on some SQL implementations such as SQL Server, there MIGHT be a rule that requires a PK because of the specifics of that version of SQL. I believe SQL Server requires a PK on any table you want to be updatable - but that isn't a requirement of "pure" SQL set theory or ANSI standard SQL.

Here is the key distinction that might have confused you. Access native tables with no dependents NEVER need a PK for relation management. HOWEVER, they might still need a PK (probably compound) or multiple independent indexes for search optimization. A PK is an index - but an index doesn't have to be a PK.

In the class table I showed you from my Navy days, there MIGHT have been a PK but it was most definitely not obvious to us. But there was a perfect candidate for a compound key based on {person, class, due-date} as a "natural" PK. Whether that combination was a true PK or the table merely had an index on each field for searching is open to question since it surely wasn't an Access table and we didn't get to see the design view.

The junction certainly did NOT need a synthetic PK (autonumber or some other computed key) because nobody would ever look it up based on the synthetic key. They would look it up based on indexes of the three fields that were members of the so-called candidate key. As long as the individual indexes existed, it didn't matter whether there was a separately declared PK.
 
Last edited:

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:23
Joined
Sep 12, 2006
Messages
15,614
partial extract

@ Minty and Dave

Nothing is being sold.
I'm not sure how much I'm allowed to disclose, but it's more like a servicecenter, which occasionally lends its bikes out of house, that has an inhouse repairshop.
So the DB is supposed to keep track of repairs made, which department, group and team the bike is currently designated to, buydate, milage, supplier and some other information for insurance/warranty purposes and some additional info for billing.

Dave. Yes, the colour is 'needed' to make it easy to visually identify the bike physically.
Your example with the car repair is about right. The thing is that I have to make 2 different 'systems' to keep track of the repairs. All repairs on inhouse bikes only need a brief note on what has been repaired, date and name of the mechanic, while all repairs on bikes designated to external users must be more detailed for billing purposes.

Maybe it's more complex than I anticipated at first, but I'll figure it out.

Thank you for both your comments.

I am pretty sure you can deal with both internal and external repairs with a single "repairs" table. The similarities must outweigh the differences. All you would need is a flag in the table to distinguish the 2.

The colour can be approximate then, I take it. Just "red" rather than a specific paint shade.
 

jjatwork

Registered User.
Local time
Today, 22:23
Joined
May 6, 2019
Messages
17
Sorry about the delay. Been off for the holiday.

@ Doc. Thanks again for the explanation. I've been doing some reading on architecture fundamentals, but there's a lot to take in all at once.


@ Dave. Yes, in theory a flag in the table would do the trick. The problem is, that I've been asked to make two different systems, one that keeps detailed track of parts used etc. and another, where the information is just typed into a string. But I'll try to rethink the design with that in mind.

The colors can be approximate, yes. They are not used for sorting, selecting or anything besides visual identification of the bike.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:23
Joined
Sep 12, 2006
Messages
15,614
@ Dave. Yes, in theory a flag in the table would do the trick. The problem is, that I've been asked to make two different systems, one that keeps detailed track of parts used etc. and another, where the information is just typed into a string. But I'll try to rethink the design with that in mind.


I would do this by having a memo notes field in the work order.

you end up with this

workorder table (include a Work Notes field)
workorder parts used table

enter some work notes on each job.
if need be, have a subform to select linked parts.

the job could just be a service, with no parts
if you need to replace a tyre, and brake parts etc, you can add the parts used.

You want to try to design the system to deal with all eventualities in a single streamlined system, It will be much easier than having multiple systems.

The colour is a red herring (sorry). You can either have a lookup table, or just free text. It only matters if you want to do a breakdown of bikes by colour - in which case you need a look up table, as otherwise you will get entry discrepancies and misspellings such as "red, red, read, pink, dark red, red multi" etc. If you have a lookup table, of course, you limit the colours to specific values.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:23
Joined
Feb 28, 2001
Messages
27,001
The problem is, that I've been asked to make two different systems, one that keeps detailed track of parts used etc. and another, where the information is just typed into a string.

Access uses the concept of SPARSE tables. In this case, the word specifically means "If you didn't need it, you don't have it - selectively."

The way I see (and apparently, the way Dave sees it too) is that EVERY bicycle that comes in gets an entry in your table that tracks . But have a "parts used" child table and, if this is "no parts needed" case then you make no entries to "parts used." And have a notes child table that uses an FK and a memo field (plus whatever else is needed, like the date of note entry and ID of the person making the note, as POSSIBLE but not necessary fields). And if there was nothing to say, you just don't say it. Then, to bring everything together, you have OUTER JOIN queries centered on the main job table being the parent and the parts and notes tables being the children that are outer-joined. And for those cases where you "didn't say anything" you get a null in the fields that you drew from the child tables. On a form this is easy to represent. On a report, having TWO child tables at once makes it trickier but still not impossible.
 

jjatwork

Registered User.
Local time
Today, 22:23
Joined
May 6, 2019
Messages
17
I don't really know the netiquette around adressing multiple/specific people, but those @'s don't feel right.

Dave, the memo notes field is a good solution, but I'm afraid have oversimplified things in my attempt to be brief. I actually need two strings, one for the repairs done under warrenty and one for the repairs payed for 'inhouse', which is how it is currently set up, but from what I can gather, some of the repairs are logged well in advance of the actual billing, in which case the notes are fundamentally the wrong approach, in my eyes, as it leads to a lot of work being done twice.
Most likely it's a matter of ease-of-use. If the secretaries get a stack of 15-20 hand-written notes, it is easier for them to type a string input, than clicking and picking all the parts.
I will try to get some clarification from my supervisor on this issue. I'm also wondering if it wouldn't be nice to have the notes as actual data, for future use.

I see your point about the colour. I'll leave it for now, in case anyone suddenly wants to know how many pink bikes there are.

Doc, as I mentioned above, this whole system of using notes seems wrong, so I think I'll have to rethink my possibilities. In any case, I'll definitely have to go back to reading some material about joins. I've done simple ones, but since restructuring the tables, the ones I need to do is more complex.

I spent some time, trying to retrofit my current forms and reports to the new structure, but it doesn't seem to be worth the time, compared to remaking them from scratch.
I think I need to push the brakes and sketch up a new model of the DB to get an overview of the additional data and functions which have crept in since I did the original.


Thank you both again for you continued input.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:23
Joined
Feb 28, 2001
Messages
27,001
In any case, I'll definitely have to go back to reading some material about joins. I've done simple ones, but since restructuring the tables, the ones I need to do is more complex.

Actually, not unexpected. Queries get complex so tables don't have to.
 

Users who are viewing this thread

Top Bottom