Integrity

[The_Doc_Man]

The very first thing you should do is study "database normalization." This will help you in designing where you need to go. But let me make a comment or two from your explanation.

I might have to use a stock number that was used previously by something else.

No. You CANNOT allow this to happen. However, let me do some quick inferential analysis. You wouldn't have said this unless you were running out of room in the field that holds stock numbers. Which means you have a small stock number and a lot of stock. The positive spin on this is that you have business growth. (Congratulations.) And now you are seeing what is commonly called "growing pains." The CORRECT way to do this is to expand the size of the stock number field. However, if you have RI enabled, you can't do this so easily.

I am betting that you are "under the gun" to get this done sooner than yesterday, but you have a technical limitation and a major rewrite ahead of you. It is time to consider that your original efforts were made from a migratory perspective (from Lotus) but the new reality of growth means it is time to consider a new set of structures.

I am toying with the idea of starting all over again and then export/import the data.

I now want to change my stock numbers. If I change the stock number it doesnt change the order form.

In fact, the best way to maintain the relationships among your data would be to redesign your structures and then do an export/import sequence into tables where your fields that were restricting you in the older design have more room for more variations.

Some other things you can do in the forum involve its Search function. When you are at the top of a forum page, there is a long blue ribbon just underneath the section that shows your login ID and the topic on which you are focused. On that ribbon, 3rd from the right, is the Search option. Look up some topics here, do some reading, and see how folks approach these kinds of problems.

Look up: "Natural Keys" and "Surrogate keys" to see some long-winded discussions on what each one is and why you would choose one over the other.

Look up: "Business Model" to see about design issues in terms of making your database match the reality of your business. From what I see, you are perilously close to a situation in which "the tail will wag the dog." That is, you are close to having the limitations of your database constrain your business decisions.

Look up: "Database normalization" for topics on how to design normalized tables. These normalized tables will help you where you have things that depend on other things, or things that describe the details of other things.

Look up: "Inventory control" - since a large part of your problem seems to be how to manage your burgeoning stock of things to sell.

Here is the question you have to ask yourself. Look at it from this viewpoint. When you have a used car that starts to need more expensive maintenance, and the frequency of that need starts to increase, at what point do you break down and buy a new one?

The computer equivalent here is, at what point will the idea to continually patch up and tweak a complex and (by your own admission) poorly designed database no longer become cost effective? You are facing the back half of that old adage: If you don't have time to do it right the first time, how will you EVER find time to do it again?

 

[Workflow]

Everything that Doc said is good advice. Having primary keys that can change is definitely poor practice. But ---- here is a picture of where you need to set cascade update. Check the box. Then when you change the value of the PK in the parent table from "abc" to "xyz" all the child records will be updated as well.

Just because you can do something doesn't mean that you should. Setting Cascade Update to true will allow you to change the values in the parent record. Once they have been changed, you should definitely go back and uncheck the box. The better solution of course is to use autonumbers but that may be a bridge too far at this juncture.

Just to reiterate, its not a Primary Key and its not a autonumber, I cannot change to cascade update because it states there is a error, about having records.

I cannot and do not wish to use autonumbers, my stock numbers are set at 5 numbers, the first 2 will identify what the product category is, so anything starting with 80xxx will be textiles, 804xxx will be textiles for adults whereas 803xx is for babywear. I hope this makes sense

 

[Minty]

It does, but I wouldn't religiously stick to 5 digits.
What happens if you exceed 99 textiles for adults or babywear?

Why not have two fields - a category code and an stock identifier. The part code could then be a the 2 parts joined for display purposes. 804-12345 etc.

 

[isladogs]

I cannot change to cascade update because it states there is a error, about having records.

That is easily fixable.
Run the unmatched query wizard to identify records missing in one or other tables.
Deal with the discrepancies by adding (or deleting) records as appropriate.
When done, apply referential integrity plus cascade update (and if appropriate cascade delete)

 

[jdraw]

Workflow,

Others have given good advice and suggestions. I'm attaching a document that also outlines database design principles that you my find useful.

Good luck.

 

[gemma-the-husky]

A surrogate key is an autonumber key which serves to identify the row. The linked tables then use the same number value to link the tables, rather than the product code etc.

That way the product code and description is ONLY in the product table, and you can change it to whatever you want.