Having trouble with Relationships in DB

dbnewbie

Hi all, this is my first mysql database , I have got all my tables in the database, I need to know how to make the tables interact with each other.

Here is what I have
MerchantID
Category ID
Merchant
MerchantLink

DealsID
MerchantID
Deals
DealsLInk
DealsStart
DealsExpire

CategoryID
MerchantID
Category

My website has several different pages (Apparel,Electronics,etc) So I need to make this database display data according to the page (e.g.Apparel).

There will be several merchants to one category
There will be several Deals to one merchant
There will be several Deals to one Start Date
There will be several Deals to one Expire Date

My question is how do I do this??
Please the simplest instruction possible....LOL I do good with visual examples....

Thanks so much for all your help! 🙂

dalecosp

You have 13 tables ?

In looking at your logic, I think I'd have three. Merchants; deals; categories.

In table "Merchant": MerchantID, MerchantName, MerchantLink.

In table "Deals": The ones you have listed in the 2nd block.

In table "Categories": CategoryID and CategoryName.

But, I don't know your issues exactly. And, I've never done a site with merchandise from more than one seller. Can you tell I don't work at Ebay? 🙂

chinni_77

hello,

I think you can design with 3 tables.

Table1: TBL_CATEGORY(category_id, category)

Table2:TBL_MERCHANT(merchant_id,category_id,merchant,merchant_link)

Table3:TBL_DEALS(deal_id,merchant_id,category_id,deals,deal_link,deal_startdate,deal_expirydate)

I've attached the design.

HTH

dbnewbie

Sorry to confuse that is basically what I have, I posted it different

I have 3 tables,
Merchant
Deals
Categories

(Each block shown above is one table)LOL

Just had some things a little different from what you showed me in your attatchment. Thanks for the Visual that realy helps!

Ok now ,

I have a page called apparel - Now I want to show Each Merchant and MerchantLink in category Apparel on that page.

How would I do this??

Shoot One more thing
With each category having several merchants do I need to repeat the categories for each merchant or just add the merchant ID's seperated with comma's? I am using myphpadmin.
Example this is what I fill out when adding merchants

        Field Type Function Null Value

MerchantID int(11) I put nothing in here
CategoryID int(25) I need to put the category number's here, if its more than one category do I just seperate them with comma's or do I complete the process for Each category?
Merchant varchar(90) I add merchant here
MechantLink varchar(180) I add merchant link here

chinni_77

Example this is what I fill out when adding merchants

Field Type Function Null Value
MerchantID int(11) I put nothing in here
CategoryID int(25) I need to put the category number's here, if its more than one category do I just seperate them with comma's or do I complete the process for Each category?
Merchant varchar(90) I add merchant here
MechantLink varchar(180) I add merchant link here

you have to complete the process for each category.Since for each category there can be many merchants.

I dont get you with your other question- page apparel???

Is apparel a category name?

dbnewbie

Yes Apparel is a category name

Ok on the other ? - Say I have merchant Kmart and it falls in category toy, apparel,electronics.

And say I have merchant toys r us and that is category toys.

Now I have to have add toys to the categories twice, correct?

Thanks so much, ya'll are tons of help!😉

dalecosp

Two things come to mind for the merchant-category issue.

One, you could put a CSV list of category id's in the category_id field of the merchants table. Read them each time, turn it into an array, and have your PHP logic only act upon those merchants in the array whose category_id array contained the correct category number.

Second, and possibly easier, would be to use regexp instead of = in your SQL, e.g.

select * from merchants.category_id where regexp = $category_number

I'm sure there are other, possibly better options.