The best way to get started a (complicated) database

[Rajko]

I used to make only some simple databases for my one use and now I have to
create a very complicated database. In short it is a price discount
database - to be used for entering discount information needed for
accounting department such as product no. and name, customer, discount % or
discount in currency (I was thinking of combining those two if possible
giving the user possibility to use which he likes and the other option would
be calculated automatically afterwards - is it possible?), period for
discount, volume minimum and target, reason for discount and the way of
giving discount.

Our product list is very large containing about 100.000 items which are
grouped to 5 tables looking like a pyramid (only about 30.000 are active so
I was thinking of making a make table query with only active products) the
ground table(X1) contains all part nr. then those are linked to X2 table to
models, then X3 to product groups and so on. I would like to enable some
sort of search in my dataentry form for the user so he does not have to type
product no and name. How can I do this with such a large product list?

Also some cost price and current sales price have to be shown on the
dataentry form so the use can se margin % and if there is base for discount.
Sales prices can be obtained from orderline table from third database which
is you can imagine huge. How can I make it lookup price for the right
product and right customer in the right time?

the same database would be used to follow up on results of discount
campaigns in terms of volume increase.
I would like to enable results to be seen by a single campaign, or by all
open campaigns, by a specific product group or by customer.
therefore I was thinking to design campaign code and campaign name to be
entered in a separate table and in the same time enter campaign code to the
price discount table with additional line no (those two would be primary key
in price discount table) Is it possible to do in this way?

I hope to hear the best way to get started the job. Any suggestion are very
appreciated.
Thanks.
Anna

 

[ss01]

Let see now start with the table first which require to be normalised and
then the relationship. Once this is firm up then you can start with the form
inputs to see whether it works as what you want. Imagine how you want the
data to be captured in the database. Test it with test data. After that
starts with Queries, this will allow you to produce the information you
want, there will be many queries as there are tables been joined together to
produce what you want since it goes hand in hand with the reports. The
report will produce very nice format that will be eye catching to anyone who
looks at it. ie management and customers. If any of the above does not works
wash and rinse and start again from the problem area. Always solve the
problem first before moving on the next since it becomes difficult to change
the database when tables are linked to forms, queries and reports, indexes
and other multitude of links between forms and queries and reports.

Regds


silvre