Help designing database!

[system]

The objective: Design a database for a local store in town to keep up with customers and their purchases.Not an online store!

The issue i’m having: How to handle the issue of repeat customers. So I have the fields they want to keep up with but on the return trip I don’t want to delete the old info instead
I want to add to it. So when the record is pulled for this person it will show all there past purchases but only on one record. I had the idea of making another database for purchases and using unique keys I would query the purchased database! Enough with the rambling. I’m just open for ideas I am a bit of a nob when it comes to the designing and would kindly like some advice links something to point me in the right direction!

Thanks in advance
Tyler Cox

[richei]

Put the purchases, items and clients in seperate tables. Then use joins to keep everything tied together. It might look something like

There’s different ways of doing client accounts, 1 is to combine companies and individuals and the 2nd is to put them into seperate tables. below is combined.

clients table
client_id - auto increment
name (either as 3 columns or 1)
company name (if its a company account)
company address
company state
company zip
contact person
contact phone
individual name
individual address
individual state
individual zip
individual phone

Items table
item id - auto increment
item name
item description
item cost
quantity discount (if provided) - like by 50 and get 25% off

Purchases table
client id
item id

that’s kinda how i would do it if i had 2. but personally, i’d suggest investing in a POS system. There are some decent free ones out there if its just for 1 register or location. There’s a lot of work involved in creating one of these and if you’re a beginner, i hope you didn’t tell them a deadline.