ERD examples 2

[ezypezy80]

Hi
I wonder if you've come accross some good
examples of data models
and ERD designs on the net.

I'm trying to create a simple but robust invoicing model for this forex trading company but the more i look into the
processes the more i get confused.

thank u

 

[BobRodes]

<simple but robust
No man may serve two masters.....

 

[ezypezy80]

true, it may not be possible
how about simple and tidy

 

[BobRodes]

All right. First, read this:

http://www.alphabetcitydataworks.com/FundamentalsOfRelationalDatabaseDesign.doc

Don't worry about ERD unless they are required deliverables or you're in a large scale development environment. If so, something like this

http://www.smartdraw.com/tutorials/software-erd/erd.htm

will help. It's much more important, though, to understand how to design and relate the tables correctly. The first article will help you with that.

Start there, and post back if you have further questions.

HTH

Bob

 

[ezypezy80]

Thanks Bob, tha articles are very good.
I managed to resolve my M-M relationships.
However I end up with tables which are linked in a circle,
which should not be the case, right?
I'll have a think about it some more.

Another question
I have a table Trade which is going to log all trades
which means it's going to grow larger and larger.
I'm thingking of backing it up every year and start with an empty table. Is this a good way of doing it?

thank you

 

[BobRodes]

<However I end up with tables which are linked in a circle,
Can you send a diagram of this?

<I'm thingking of backing it up every year and start with an empty table. Is this a good way of doing it?
Sure. Year end archiving is fairly common.

Bob

 

[ezypezy80]

yes in visio, but i cannot attach a file here?

 

[BobRodes]

No, you can't. Can you just send along the table names and key fields?

 

[gmmastros]

<I'm thingking of backing it up every year and start with an empty table. Is this a good way of doing it?

Instead of backing it up, you could do 'Horizontal Partitioning'. This link is pretty good at explaining it.

http://www.sqlteam.com/Item.ASP?ItemID=684

This is specific to SQL Server, but the concept can be used with any database (but partiioned views may not).

My point is... Instead of backing up the table every year and starting over, you could have a table for each year and then move the data from the active table to the 'archive' table. This keeps all the data in the database and allows you to run historical reports without having to go to another database to do it.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ezypezy80]

>No, you can't. Can you just send along the table names and key fields? BobRodes

Product
ProductID

Trade
TradeID
ProductID (FK)

TradeDetails
TradeID (FK)
ClientID(FK)
**1 Trade always have 2 clients involved
E.g
TradeID#1 Client#A as Buyer
TradeID#1 Client#B as Seller
TradeID#2 Client#A as Lender
TradeID#2 Client#B as Receiver

Client
ClientID

Status
ClientID (FK)
ParticipantID (FK)
**I actually don’t need to record Client and Status pairs
but I need the Status to appear in tradeDetail table ???


Participant
ParticipantID (FK)
ParticipantType i.e. buyer, selller, payer, receiver,lender, borrower

ProductParticipant
ProductID (FK)
ParticipantID (FK)
**1 Product always have a 2 types of participants
e.g Product#1 involves a buyer and a seller
Product#2 involves a lender and a borrower

 

[BobRodes]

I like george's suggestion.

If a trade always has 2 clients involved, in separate capacities, then you can have

TradeDetails

TradeID
ClientTo(FK)
ClientFrom(FK)
TradeType (could relate to a types table)

<but I need the Status to appear in tradeDetail table ???
Looks right.

I'm not quite following what you're trying to do with products and participants, and how they relate to trades?

Also, do you have multiple transactions in a trade? And can they be with different participants?

Bob

 

[ezypezy80]

Thanks George I will read the article.

>I'm not quite following what you're trying to do with products and participants, and how they relate to trades?

Thanks for the feedback Bob, I relate Product to Participant because 1 Product has certain participantTypes.

In a "Add new trade" form, I want to have something like this:
Select Product from listbox e.g. Bank Bill, then show its participantTypes i.e. lender and borrower
then select client for lender, and client for borrower.

Then I want to save both clients and their participantTypes in the tradeDetail table.
I think I'm confusing you even more )


>Also, do you have multiple transactions in a trade? And can they be with different participants?

Yes, that's why I have tradeDetail table.
1 Trade always involves 2 clients, who will be buyer/seller, or lender/borrower or payer/receiver.
But things like product, rate, date of a trade is the same for both clients.

 

[BobRodes]

<Then I want to save both clients and their participantTypes in the tradeDetail table.
I think I'm confusing you even more

No, don't think so, it looks pretty close to what I suggested with TradeType, doesn't it? I'd say you're on the right track.

I'd also say that if you study the NWIND.MDB database that comes with Access, you'll see some things that are similar to what you're trying to set up. Orders, OrderDetails, Customers, Products.

HTH

Bob

 

[MichaelRed]

If you intend to "archive" data in either of the above manners, you need to also consider other issues in hte db design. In particular, relationships with the "archived" data can cause many issues. Another real problem can arise from this when attempting to generate meaningful aggregates. If you "archive" on (or about) Jan 1 and are attempting to generate any type of running aggregate, they will all revert to (somewhat) meaningless drivel at that time.



MichaelRed