MartinPetex
asked on
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
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
VBClassicGuy
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).
ASKER
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.
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.
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
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
ASKER
Rimvis,
Making my diagram look like the attached....?
Doesn't this make tblTrade.trader_id obsolete?
Thanks.
tables2.JPG
Making my diagram look like the attached....?
Doesn't this make tblTrade.trader_id obsolete?
Thanks.
tables2.JPG
ASKER
....also am I right to make tblTraderTrades.trader_id and tblTraderTrades.trade_id both primary keys with no identity specification?
Thanks.
Thanks.
>>Doesn't this make tblTrade.trader_id obsolete?
Yes it does.
Also, you should remove trade_id from tblTradeType
Yes it does.
Also, you should remove trade_id from tblTradeType
>> am I right to make tblTraderTrades.trader_id and tblTraderTrades.trade_id both primary keys with no identity specification
Yes
Yes
ASKER
Rimvis,
Resulting in the attached....?
If so, I will test this out and then reward points if I have no issues....thanks.
tables2.JPG
Resulting in the attached....?
If so, I will test this out and then reward points if I have no issues....thanks.
tables2.JPG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.