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

Do I have the correct Database Design?

Hi,

I have the attached database design and the attached linq select query. Unfortunately when I view my frontend the results of the query display repeated trader_surname, trader_firstname and trade_name values for every trade_type_name value. I want to show distinct trader_surname etc but due to trade_type_name values all being different I can't seem to achieve this. Is my database design wrong?

Thanks.
var query = (from tt in db.tblTraders
                     join ttcl in db.tblTraderCityLookups on tt.trader_city_code equals ttcl.trader_city_code
                     join lctt in db.tblTraderCountryLookups on tt.trader_country_code equals lctt.trader_country_code
                     join tcl in db.tblTraderCountyLookups on tt.trader_county_code equals tcl.trader_county_code
                     join trade in db.tblTrades on tt.trade_id equals trade.trade_id
                     join tradet in db.tblTradeTypes on trade.trade_id equals tradet.trade_id into tc
                     from tradet in tc.DefaultIfEmpty()
                     orderby tt.trader_firstname ascending
                     select new { tt.trader_id, tt.trader_firstname, tt.trader_surname, trade_type_name = tradet.trade_type_name == null ? "" : tradet.trade_type_name, trade.trade_name });

        e.Result = query;

Open in new window

db-design.jpg
0
MartinPetex
Asked:
MartinPetex
2 Solutions
 
Naithan ArroyoCommented:
Your data base deisgn can rarely be wrong your query can be accomplished  By Taking out the column that Repeats the results and add that data later.
0
 
MartinPetexAuthor Commented:
Do you have code examples to show how I can do this? Not too sure what you mean by "add that data later".

Thanks.
0
 
David L. HansenProgrammer AnalystCommented:
I think I'd add a "trade_type_id" field to your tblTrade table.  This is so each trade will get it's own unique id along with whatever name or description you want (or your client) wants to give it; along with that, a link to the tblTradeType table is make with the foreign key I just mentioned above (trade_type_id).  So when you are selecting trades later on for reporting you just join tblTrade with tblTradeType ON trade_type_id.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Fernando SotoRetiredCommented:
If your test DB is not large and you can Script the tables with data I will look at it to see what can be done.
0
 
MartinPetexAuthor Commented:
sl8rz,

Is that not what the trade_id column in my tblTrade currently does?

Thanks.
0
 
MartinPetexAuthor Commented:
sl8rz,

What I mean is, a trade can have many trade types and a trade type can only have one trade - so the link between tblTrade and tblTradeType will have to be a one to many on the tblTrade.trade_id to tblTradeType.trade_id, meaning I cannot link on trade_type_id even if it is within in tblTrade, this column would then become obsolete....no?
0
 
David L. HansenProgrammer AnalystCommented:
What I'm thinking of is this:
If you are to open a hypothetical record in your tblTrade table using my methodology you'd see something like this:
trade_id: 1501,    trade_name: "Jim's first trade",    trade_type_id: 5

Now tblTradeType would have an entry of:
tradeTypeID: 5,  trade_type_name: "Sell Put Option"

You see, tblTradeType would list all the possible types of trades (it would have a small number of records compared to the tblTrade table.  So if you ever need to change or add any trade types, you just do it in the database and any applications that use it get the change automatically (without having to change any of the applications' code)...a straddle trade perhaps.
0
 
MartinPetexAuthor Commented:
sl8rz,

Would the attached work better....?

Thanks.
tables2.JPG
0
 
David L. HansenProgrammer AnalystCommented:
Yes, that is exactly what I was thinking.
0
 
David L. HansenProgrammer AnalystCommented:
Although I think you can get rid of the tblTraderTrades table...I'm not sure it does much for you.
0
 
David L. HansenProgrammer AnalystCommented:
You'll only need tblTraderTrades if more than one person can make a single distinct trade...which I don't think is possible is it?
0
 
MartinPetexAuthor Commented:
sl8rz,

I created tblTraderTrades to handle the situation when different traders have the same trade, do you not think this table deals with that? How would you deal with that situation?

Thanks.
0
 
David L. HansenProgrammer AnalystCommented:
I guess you might need tblTraderTrades if different traders can share a single trade between them...I don't know how that might happen.  The idea is that tblTrade logs all trades individually and stores a unique id for each.  So if more than one trader can own a single unique trade (made on a specific day and time, etc.) then it makes sense to have the tblTraderTrades.
0
 
MartinPetexAuthor Commented:
The concept is that this database is to be the backend of my tradesman web site. A potential trader will navigate to my web site and insert their name, address etc along with their trade and trade type from preset drop down boxes into my database through a frontend GUI, i.e. Joe Bloggs, 1 Microsoft Way etc, Builder, Houses. When you say "if more than one trader can own a single unique trade" do you mean at a specific time or at any time?...as traders are bound to insert themselves against the same trade and trade type at some point. 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) 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.

Thanks.
0
 
David L. HansenProgrammer AnalystCommented:
I see a trade listed in tblTrade as this kind of record:
John Doe bought 100 shares of Apple Computers on the 16th of May 2010 at 2:00 in the afternoon.

Now that is a single unique trade.  Those details are captured in tblTrades (although there is an id instead of a persons name) the ideas is that someone purchased something (a stock, an option, whatever) and that transaction is recorded in tblTrade.  Perhaps you need to clarify what kind of trades you want to capture...I've just assumed they are stock market type trades, but if not please correct me.  Also, how do the customers come into play?
0
 
David L. HansenProgrammer AnalystCommented:
To really flesh this out (if you are doing a truly transactional kind of system...ie. tracking real stock trades) I think you should add into tblTrade the following fields (assuming you delete tblTraderTrades):

keep "trade_id", add "trader_id", change "trade_name" to "trade_desc" (not necessary), leave "trade_type_id", add a "trade_dateTime"

If you are tracking stock exchanges, option exchanges, and even currency exchanges, etc. I'd build a separate table for each type of exchange (ie. currency exchanges will have very different details than a stock trade).  Each of these tables will have the id from tblTrade in them as its key.  So if I bought a stock then I'd have both an entry in tblTrade and in say...tblStockTrade.  tblTrade says I did a trade and when, but tblStockTrade has the specific details of that trade (and the same trade_id listed as it exists in tblTrade to tie the records together).
0
 
MartinPetexAuthor Commented:
sl8rz,

Sorry that I've not been clear but my web site is not for trading in the stock market etc but for collating a database of tradesman (Builders, Roofers, Plumbers etc) for customers to search in regards to completing a job for them, i.e. a tradesman web frontend for traders to enter their details and a customer web frontend for customers to select a tradesman for work. Currently I am trying to create the correct db design for the tradesman side.

Typical data to be found in the tblTrader table is:

trader_id: 1
trader_firstname: John
trader_surname: Doe
etc

Typical data to be found in the tblTraderTrades table is:

trader_id: 1
trade_id: 1

Typical data to be found in the tblTrade table is:

trade_id: 1
trade_name: Plumber
trade_type_id: 1

Typical data to be found in the tblTradeType table is:

trade_type_id: 1
trade_type_name: Pipework

Ultimately as I said before I am going to build another similar frontend that will insert data into the same database (within different tables) 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. I will also be creating columns for payments details etc but I'm just trying to get the basic db design created initially to make sure data integrity held.

Thanks.
0
 
JestersGrindCommented:
Your design looks good.  The tblTraderTrades table is important to establish the many to many relationship between traders and trades, meaning many traders can have the same trade and a single trader can have many trades.

Greg


0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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