Xrhrft

I want to create a database system that is user friendly with the right relationships. My system should be in such a way that we have a writer's table, orders table, and payments table such that I am capable of knowing which writer did certain orders and shows the amount the writer should be paid for doing an order. The system should also indicate whether the payment has been made for each order done by the writer.

I tried my best and came up with the following tables:

• payments table: pay_id (PK), date, mode of payment, amount, balance, writer_id (fk), order_id (fk);


• writer’s table: id (pk), name, email, phone numbers, disciplines;


• order’s table: order_id (pk), name, order_status (completed, under warranty, completed), writer_id (fk)

Kindly assist me in making the necessary corrections so that all relations can be created and the database becomes human friendly.

I have absolutely no idea what a user friendly database could possibly look like. Your apps are like the dashboard and controls of an automobile. This is where you might hear the expression user friendly. The database is like the engine and/or transmission. They have important functions to perform and here is where you would expect to hear expressions like reliable.

Your top concern when designing a database is data integrity. That is what gives it the aspect of reliability. Without that, the most user friendly interface in the world would be useless.

There is, however, one section of a database that we might concern ourselves with user friendliness, if by user we mean app developer. That is the api or the interface to the database. The app developer doesn't really care about relationships and really shouldn't be concerned with the physical layout of the database. Sure, in their code one entity may relate to another, but the physical implementation of that relationship in the code may be (and generally is) very different from the physical implementation of the relationship in the database. No computer language has "foreign key" as one of its features.

What the app developer needs are:

1. Reliable data


2. Fast retrieval and storage of the data


3. Data in a format easiest to handle within their coding framework

The first need is delivered by designing the database with data integrity foremost in mind.

The second need is delivered by a good combination of software (efficient queries and stored procedures) and hardware. Very rarely will efficiency require a change in the design of the data, such as denormalization.

The third need can be delivered by a variety of methods which can vary according to the particulars of DBMS and front-end platform but chief among them are a library of stored procedures (heavily overridden to supply an interface for data in a wide range of forms) and views (exposing the data for direct access also in a wide range of forms).

One rule that seems to apply over all three of these needs is this: do not allow developers direct access to the tables. Find out what data they need and in what form they need it, then supply the stored procedures and/or views that will give it to them. An actual api written in the app language would be a great addition. Then the developer doesn't have to think of the database at all, just the api.

This rule helps in several ways but by far the most important is that you will not need to change the design of the database to satisfy the needs of the application developers. Database designers who find themselves in the situation where a database design change is forced by developer needs almost inevitably do so by sacrificing data integrity. When you find yourself going down that road it is all down hill -- and not in a good way!

With a wall of abstraction between the data and the application, when the database needs to change because of changes to entities or relationships, those changes can be made in a way that maintains the all important reliability and then the stored procedures and views changed to maintain the expected interface with the now-changed data. When the application adds or modifies functionality, this can change what data it needs or the form it needs it in, but this does not require a change in database design to fulfill those changing requirements.

Life is good.

In your particular situation, you have Writers, Orders and Payments. This seems like a fairly straight-forward design which you have made a pretty good start. With just a few tweaks (the Payments table doesn't need a writer field -- that is contained in the Order), you should have it nailed down in short order.

Then answers to such questions as "whether the payment has been made for each order done by the writer" can be supplied by stored procedure or view.

If you get to that point and run into trouble, ask another question.