?
Solved

Do I have the correct Database Design?

Posted on 2010-01-11
20
Medium Priority
?
336 Views
Last Modified: 2013-11-11
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
Comment
Question by:MartinPetex
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
20 Comments
 
LVL 5

Expert Comment

by:Naithan Arroyo
ID: 26284104
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
 

Author Comment

by:MartinPetex
ID: 26284379
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
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 26284432
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
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 
LVL 63

Expert Comment

by:Fernando Soto
ID: 26284736
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
 

Author Comment

by:MartinPetex
ID: 26284961
sl8rz,

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

Thanks.
0
 

Author Comment

by:MartinPetex
ID: 26292022
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
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 26294056
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
 

Author Comment

by:MartinPetex
ID: 26294363
sl8rz,

Would the attached work better....?

Thanks.
tables2.JPG
0
 
LVL 15

Accepted Solution

by:
David L. Hansen earned 1600 total points
ID: 26294382
Yes, that is exactly what I was thinking.
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 26294408
Although I think you can get rid of the tblTraderTrades table...I'm not sure it does much for you.
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 26294437
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
 

Author Comment

by:MartinPetex
ID: 26294866
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
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 26295011
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
 

Author Comment

by:MartinPetex
ID: 26295167
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
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 26295328
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
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 26296051
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
 

Author Comment

by:MartinPetex
ID: 26302614
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
 
LVL 21

Assisted Solution

by:JestersGrind
JestersGrind earned 400 total points
ID: 26323147
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question