I have 2 questions about database designing with MS-ACCESS

[boulifb]

Hello gurus,
I'm currently building a database under Access. I have 2 questions about the problems I have:

- I wish to know how to handle regular orders; for example a customer orders the same products every 3 months.

- I have security levels in the database. People can be sales person, supervisor, manager, administrators. The lowest level is the sales person who can access only to its customers. supervisor has access to his group of sales persons, manager has acces to his group of supervisor and administrators see all. I have three tables: one describes the Security levels, one describes the Users (with login, password, fk_security_level, fk_employee, etc.) and the last one is the employee table. How can I handle this problem with ms-access?

Thanks for the help.

Best regards.

Fred.

Note: the database is under MS-Access and the application will be developped in C#.

[ahoodin]

Well I would probably make another table for regular orders. I would call that the schedule table.

The C# app checks the schedule in the morning then make SQL inserts to your order table.

I would consider using MySQL, because Access is limited.

The security part could be handled with an ID badge using either RFID, barcodes or just typing a password. In order to make a transaction, just require a password.

In order to secure the data, use client server. Put the data on the server, use the client to enter orders, enter security codes, and display data. Use the server to retrieve data, validate security and act as a central reposititory for orders and such. Data could be encrypted.

ahoodin

[boulifb]

Hi,

Thanks for answering.

I have dispatched the orders like this:
- Customers (table that contains the usual customers information)
- CustomersOrders (PK_CustomersOrders, Date, FK_VAT, Total_Without_Tax, Total_With_Tax)
- CustOrdersLines (PK_CustOrdersLines, FK_CustomersOrders, FK_Products, Quantity, Total_Line_No_Tax)
- Order: relation between Customers and CustomersOrders containing FK_CustomersOrders, FK_Customers to retrieve all orders from a given customer.

The relations are like this:
Customers (Customers) order (Order) products (Products) in an order(CustomersOrders) to the company.
An order (CustmersOrders) is composed of lines (CustOrderLines).

So, for automatic order scheduling, the application should recompute the Date field from the CustomersOrders table? Maybe I need also to add a field "AutoOrder" and "Interval" in the Order relation to tell the application to recompute the date with the Interval?

Concerning the users, I can't do something too complex as the company is far far away from me, at the opposit of the planet. Thus, the company wants something simple. They are not IT professionals. The security level of the company is not Microsoft, Oracle, the CIA, FBI or KGB. There is no need to encrypt data. I intend to put Logins in a table. This table is named Users and contains the following fields: PK_Users, FK_Security_Level, Name, Password, FK_Employee. I can easily make the relation between users and their customers by including a relation that contains the user ID and client ID, but my problem is to handle users together as I mentionned in the begining of the topic. I don't know how to handle user levels, except the administrator ;)

For the database, I have no choice but using MS-Access because the development is for a tiny company and the database won't that big (less than 50MB), and it is also the will of the company.


Thanks for helping me to solve this :)

Best regards.

Fred.

[ahoodin]

Use ODBC so you have a chance to switch databases or upscale to SQL Server or MySQL in the future.

So, for automatic order scheduling, the application should recompute the Date field from the CustomersOrders table? Maybe I need also to add a field "AutoOrder" and "Interval" in the Order relation to tell the application to recompute the date with the Interval?

Yes microsoft allows for easy time date computations with CTime objects and the like. You can add to dates and subtract from dates. The auto order could be based on a period in the table that is every x days from the start date.

ahoodin

Dont forget to rate.

[boulifb]

ok,
I added an "AutoUpdate" and an "Interval" field in the "order" relation. I think this way it'll work.
For the users management, I think I have the clue. I will use the FK_Security_Level from the user in the SQL queries. It should work.

Thanks for your help :)

Best regards.

Fred.

[ahoodin]

Your welcome.

ahoodin