An advice about a suitable Database design and implementation with C#

Stores Table : a table to store information about different stores

StoreId     Name       PlaceCode  
--------    --------   --------     
1           Addidas     AB1         
2           Nike        KS3         
3           Puma        LS6         
4           Nike        AS2         
5           Nike        WR4

Areas Table

AreaId   Area
------   -------
1        DownTown
2        UpTwon

StoresArea Table : Edit

StoreId   AreaId
-------   -------
1         1
2         2
3         2
4         1
5         2

Nike for example has more than a branch for it's store so my idea was to have more than a record with name attribute "nike" but the other attributes will contain the information about the branch.

I used the StoresArea Table to create a many-to-many relationship but I think it can be achieved with a one-to-many relation...and it's confusing, so I was hoping for a good advice so it could help me follow a good practice!

I created a class named store, to hold the information about each store..but i want to add an additional variable and I think it will be an array, this variable will hold information about all the branches of a given store.

I think this information will be:

1 - the branch's PlaceCode

2 - Id so I could provide a url using querystring to access all the other branches ?StoreId=Id

3 - the branch's area

to go straight to the point, i want the detailed page of any given store to have a field called branches, which will display the Branch's Area + the PlaceCode of all the other available branches. E.g "Branches : Downtown: AS2"开发者_JS百科 and the Downtown word will be clickable to link to the branch!

I know It's not clear enough! but I hope you'll get it sure, you can ask about any further details or questions make the picture more clear!

Kashef,

Every store is only in a single region, correct? If so, create a store table with a foreign key relationship to the region table (i.e., RegionID in the store table).

If different stores are part of the same brand/company, create a company table and set up a foriegn key to the store table.

The setup would look like

Region Table ---> Stores <---- Company

Is that the situation you have? If so, let me know and I'll talk about the classes.

Erick

Edited based on comments

In that case, what you really need a many-to-many relationship between Regions and Stores, with a distinct primary key (i.e., not RegionID + StoreID).

Region Table ---> RegionTable <--- Stores <---- Company

This will allow you to have multiple stores in a single region. However, it also allows multiple regions for a single store! You might be able to work around this with a constraint that ensures that each store only has a single region.

Does this make sense?

Erick

As a general rule, I recommend using the business language to define your software concepts. So, I would use the word "branch" to define the concrete, physical place where people can buy stuff. I would make defining this shared business language pretty much the first step in the project.

It also helps to then use this business language in a semi-formal syntax to work through the concepts. I think that you say the following.

• There are many companies (Nike, Adidas etc.)
• Each company has one or more branches.
• A branch has one, and only one area code.
• One area code may have 0 or more branches.

In that case the model is the first solution Erick T suggests (forgive me for not using the same names he does).

Specifically:

Table COMPANY

COMPANY_ID     NAME
---------------------
1              Nike
2              Adidas
3              Reebok

Table AREA

AreaId   Area
------   -------
1        DownTown
2        UpTown

Table BRANCH

BRANCH_ID       COMPANY_ID       AREA_ID       ADDRESS
-------------------------------------------------------
1               1                1             The Mall
2               1                2             High Street
3               2                1             Near MacDonalds
4               3                2             Out of town

To find other stores for the company for a given branch, you only need the branch ID:

select *
from branch
where company_id =
 (select company_id from branch where branch_id = %current_branch_id%