• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 167
  • Last Modified:

Do I have the correct database relations?

Hi,

I have the attached database diagram and relations for the backend of my tradesman web site. The concept is that a potential trader will navigate to my web site and insert their name, address etc along with their trade and trade type into my database through a frontend GUI, i.e. Joe Bloggs, 1 Microsoft Way etc, Builder, Houses. Do I have the right relations set up for this to work correctly? Should the relationship between my tblTrade table and tblTradeType table be a one to one relationship instead of a one to many as I do only want each trade for a specific trader to have one trade type? -  Another aspect I must mention is that ultimately I am going to build another similar frontend that will insert data into the same database (within different tables obviously) but this frontend will be for customers and not traders. The customer will insert themselves against a trade and trade type (the work they require) chosen from two drop down boxes on my frontend that are populated from the tblTrade and tblTradeType tables respectively.

Any help appreciated.
tables.JPG
0
MartinPetex
Asked:
MartinPetex
  • 4
  • 4
1 Solution
 
VBClassicGuyCommented:
Can't tell. Looks like the tblTrader table has the trader_title pointed to. Should be trader_id (can't mix nvarchars and int's).
0
 
MartinPetexAuthor Commented:
VBClassicGuy,

Sorry if my diagram isn't clear but I am using the trader_id column as my llink between tblTrader and tblTrade. The relationship link labels show the joining column.

Thanks.
0
 
RimvisCommented:
If "trade type" is an attribute of "trade", you should have FK trade_type_id in tblTrade:

tblTrade
   trade_id
   trader_id
   trade_type_id
   trade_name

tblTradeTypes
   trade_type_id  
   trade_type_name

Also, how ar you going to handle situation, when different traders have same trade? Maybe you should have separate table for list of possible trades, and a table, that links traders to trades, e.g.

tblTraderTrades
   trader_id
   trade_id

0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
MartinPetexAuthor Commented:
Rimvis,

Making my diagram look like the attached....?

Doesn't this make tblTrade.trader_id obsolete?

Thanks.
tables2.JPG
0
 
MartinPetexAuthor Commented:
....also am I right to make tblTraderTrades.trader_id and tblTraderTrades.trade_id both primary keys with no identity specification?

Thanks.
0
 
RimvisCommented:
>>Doesn't this make tblTrade.trader_id obsolete?
Yes it does.

Also, you should remove trade_id from tblTradeType
0
 
RimvisCommented:
>>  am I right to make tblTraderTrades.trader_id and tblTraderTrades.trade_id both primary keys with no identity specification

Yes
0
 
MartinPetexAuthor Commented:
Rimvis,

Resulting in the attached....?

If so, I will test this out and then reward points if I have no issues....thanks.
tables2.JPG
0
 
RimvisCommented:
>>Resulting in the attached....?
Yep
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now