CODEWITHSUNDEEP
mysqldatabasedatabase-design
xur17
xur17

Reputation: 516

Mysql Database Layout

I have created the following database layout, and started coding the application. The more I read, the more I realize my database layout is probably incorrect / inefficient / a bad idea. Before I develop too much code using this layout, I want to make sure I am doing it "correctly".

Basically I have a list of ~2000 stores, and a list of ~50 promotional codes. I need to store whether or not each code is valid at each store. Right now I chose to store each store number as a column header, with the first column containing all of the different possible codes. Here's an image of part of the table so far (1 represents the code being valid, 0 invalid at that store).

The promotional codes will change relatively frequently, but the store numbers should be relatively static, and not change very much.

This is my first time creating a database from scratch like this, and I am a beginner at using mysql, so any help is much appreciated!

Upvotes: 0

Views: 310

Answers (2)

Sam
Sam

Reputation: 10113

This type of relation is called many-to-many. I typically have three table for this type of situation. One table for the stores, one for the promo codes, and a third relational table that would have two columns: the store id and the promo id.

Upvotes: 2

haltabush
haltabush

Reputation: 4538

You should better use a table for your stores. If you don't, when one store will be added you'll spend a lot of time to add a new field...

Here is what I would do:

table store will contains your 2000s stores
    id
    name

table code will contains your 50 codes
    id
    name

table code_store will contains only the valid codes ID, and related store_id(no need to save invalid ones I guess)
    code_id
    store_id

Upvotes: 2

Related Questions