Database creation help

[684425]

I am working on a database (filled with history data) for the following,
Person A is running a business as a third party between multiple sellers B and multiple buyers C

1) A purchases bikes from B and maintains the following data:

"Invoice ID of C (or blank), Name of B" , "Bike brand, model(year), color(red or black), engine number, chasis number, registration number, registration status (1 of 3 posibilities), file status (1 of 4 posibilities), copy status (1 of 4 posibilities), number plates status (1 of 4 posibilities), price, deal date, delivery date"

2) A sells bikes to C in installments and maintains the following data:

"Invoice ID (not blank but one duplicate found in history data), Name of C, contact number, ID card number", "Deal date, Price, advance, advance taken by, remaining amount after advance", "Installment number, collection month, date on which it was collected, collected by, total collected amount including advance, total balance amount"

I was given this task from point number 2 but after facing problems specially in expenses and balance sheet. now i have to start from zero.

The idea that (in my opinion) suits the above is,

1. Vendors table

2. Items table

3. purchase table

4. payments (to vendor) table

5. Clients table

6. Sales (to clients) table

7. installments (from clients) table

But the problem i am facing is that i am failed to distribute the above fields between these tables. Please guide me if i am wrong at some point

Edited July 27, 2020 by 684425

[requinix]

What fields do you think should go in which tables, and what fields are you not sure about?

[Barand]

Your items table may be a headache.

Vendor B sells bikes and the item attributes are

Quote

Bike brand, model(year), color(red or black), engine number, chasis number, registration number, registration status

Now what if vendor D sells fridge/freezers whose attributes are

width, height, depth, colour, fridge capacity, freezer capacity, ice dispenser(Y/N)

and vendor E is selling concert tickets?

[684425]

3 hours ago, requinix said:

What fields do you think should go in which tables, and what fields are you not sure about?

Sir, I think it is...

1. Vendors table
(vendor ID,  name, contact)

2. Items table:
(Item ID, invoice number 'varchar', Here every item is a bike 'varchar', different brands but duplicates 'varchar', different models but duplicates 'year', only two colors mostly red so duplicates 'varchar', engine number unique 'int', chasis number unique 'int', registration number unique 'varchar', statuses of registration, file, copy and plates are as 1 of 3 or 1 of 4 choices (radio buttons will be used on frontend), deal date 'date', delivery date 'date'

3. purchase table:
(vendor ID, item ID, cost (separate for each bike))

4. payments (to vendor) table:
(ID, vendor ID, payment amount, payment date, paid by)

5. Clients table:
(ID, name, ID card number, contact number)

6. Sales (to clients) table:
(ID, vendor ID, item ID, invoice number (not blank in this table but one record in history data is duplicate), date of buying, price, advance, paid to, total paid (including installments), total balance)

7. installments (from clients) table:
(ID, sale ID, amount per month, paid amount (can be less, equal or more than per month), remaining (can be less than, equal to or greater than zero), payment month (on which it was to be paid), payment date, paid to)

I am trying hard to make a database, compatible with manual data and old database too (old database contains errors but my friend is satisfied with it as it is the exact copy of his manual data) but on the other side i want to do things the right way (i think the fields in each table are not as they should be)

Edited July 27, 2020 by 684425

[684425]

2 hours ago, Barand said:

Your items table may be a headache.

Vendor B sells bikes and the item attributes are

Now what if vendor D sells fridge/freezers whose attributes are

width, height, depth, colour, fridge capacity, freezer capacity, ice dispenser(Y/N)

and vendor E is selling concert tickets?

Sir, this is only for bikes. I am sorry i forgot to mention it in my post.

[Barand]

I wouldn't split the clients into vendor and client tables - just have a client table. In one transaction, A might be the seller but in another transaction A might be the buyer (as in the examples in your initial post). Just record the buyer and seller ids in each sale transaction.

Don't store derived data, such as total paid, balance outstanding etc. You get those by querying the transactions and payments.

Edited July 28, 2020 by Barand

[684425]

3 hours ago, Barand said:

A might be the seller but in another transaction A might be the buyer

No sir, A are the companies (always sellers), C are individuals (always buyers)

 

3 hours ago, Barand said:

Don't store derived data, such as total paid, balance outstanding etc.

Agree sir, I am confused in both database structure and frontend, so i added this. But it is not added now.

Currently i am working on Excel, i will post screenshots tonight in next reply IA.

[684425]

8 hours ago, 684425 said:

Currently i am working on Excel, i will post screenshots tonight in next reply IA.

I have added an image,

User requirements are:

Vendors, items by vendors and payments to vendors

How many vendors are there?

Which vendor has sold him what on which date (and what is the status of registration, file, copy and plates)?

How much amount (each transaction by date) has been paid to a specific vendor and what is the current balance?

Clients, items by invoice number and payments collection from clients

Which client has bought of which vendor's item on which date?

Who handled the client, filled the form and received advance from client? (There are three persons, user, his father, his brother. Form filling and advance receiving must be done by the same person)

Which client has paid all the amount? (in full or in parts) the status must be shown as cleared for this category

Which client is still paying installments? (is he/she paying on time or not? if yes then status shown as pending else defaulter)

If a mistake by one of those who handle clients, is found then status should be as error

Daily, weekly, monthly, yearly etc summary of collected payments can be generated

(I may have missed something, i will add it later. Also i will try to complete database structure and upload soon IA)