0

I've been working on a project lately that is written in python and I want to incorporate mysql into it. The python code is basically a sales report writing system in which every sales report will have it's own number such as 10-0001, 10-0002 and will contain customer information like first & last names, addresses, phone numbers, etc. There is also a page, if needed, for items such as televisions, brand names of items, color of items, serial numbers of items, values of items, etc, and there will also be a narrative page where several paragraphs of info can be entered.
I originally made a database named "maindb" which has a table named "info". Inside of the table I made a few columns such as "fname", "lname", "City", "State", "Brand","Item". I now want to go through my entire code and set up all of the entries fields with mysql but I'm not sure if I should make it so every report number creates a new table with new columns, or if I should just stick with one table and a whole lot of columns in it.

Info to be entered can include:

The employee
sales report number
date & time of sale

customer's first & last name
customer's address
customer's phone numbers (cell, work, home)
type of payment (cash, credit card, check)

number of items
item description
make of item
model of item
serial number of item
price of item

sales narratives/notes

I read about 'normalization' which seems like something I could use but I'm not really sure how to go about that. Any suggestions? Thanks

2
Contributors
5
Replies
6
Views
6 Years
Discussion Span
Last Post by smantscheff
0

Sorry about the 'code' tags.....couldn't edit the post.

0

Sorry about the 'code' tags.....couldn't edit the post.

1

Normalization means in effect that you store any piece of information exactly once.
A standard report format may (and should) be based on such a normalization, but contains lots of repeated information and structures. It does not make sense to map a report format 1:1 to a database design scheme.
Start with designing one table for customers, one for items, one for employees and then one for sales and for sales items, which link them all together.
A table named "info" is a guarantee for a troublesome database application. After all, which table does not contain "info" -?

0

Ok, thank you for the reply. I will start a new database with at least 5 tables (more appropriately named). Would it make sense to add a foreign key to the sales items bought, to point to the customer table? I'm trying to read up on it.

0

Customers, Items and Employees don't need foreign keys, representing independent stand-alone real-world objects.
Sales belong to Customers and Employees (1 Employee sells to 1 Customer N times), and SalesItems belong to Sales (1 Sale consists of N SalesItems).
So Sales and SalesItems need foreign keys into their master tables.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.