DB Design question

Question

DB Design question

Ken McLane

Grasshopper

Points: 16
More actions
March 29, 2006 at 10:25 am

#189932

http://www.centralbancard.com/sqlfile/DIAGRAM1.pdf
This is where I have gotten to so far, but I think I'm off track. The buyrate table should contain information about buyrate items, i.e. transaction, wireless access, help desk calls, and many others which I have counts for in the expense table. Each agent will have different costs associated for each buyrate. Not all buyrate items will apply to all agents. I'm getting confused. I think I need to break my tables down further. Any advice is appreciated. I think I did ok with the agent and merchant parts, but this is my first real db.
Ken

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

vadba SSChampion Points: 11133 More actions · Answer 1

You didn't provide enough information to properly analyze the situation. You've placed sortcode and groupid into both the agent and the buyrate table - I'm not sure why. I will ignore the groupid for now, but assuming that the sortcode in the buyrate table is a foreign key related to the sortcode in the agent table, then you could consider try this approach as a starting point:

agent (sortcode PK, agent, groupid)

|

1:M related on sortcode

|

agentBuyRate ( sortcode PK, buyrateid PK, cost, ... )

|

M:1 related on buyrateid

|

buyrate (buyrateid PK, description, incomeExpense, type, rate, groupid)

You said that the costs of each buyrate vary for each agent, so I moved that column into agentBuyRate. I assume the rate is the same for all agents. If the rate also varies, you'll need to accomodate that in the agentBuyRate table as well.

When diagramming, I prefer to place the Primary Key column first - it's easier to read and understand.

Have you read about normalization (i.e. first normal form, second normal form, third normal form, etc)? If not, please do so. There is plenty of info on the web. You should always normalize your design to at least third normal form. When physically implementing the database, you can always selectively denormalize if necessary.

Ken McLane Grasshopper Points: 16 More actions · Answer 2

Thanks, I knew I was missing something. I did read about normalization, but still haven't quite grasped the concept, or rather the implementation. Each buyrate can have either a cost OR a rate, they might even have both, so they do both need to be moved.

I see you use 1:M and M:1 which I understand as one to Many and vice versa, is this a different setting somewhere or is it set by which table is the "master"?

vadba SSChampion Points: 11133 More actions · Answer 3

The relationship between agent and buyrate is many-to-many. Thus, you need another table (agentbuyrate) to create two one-to-many relationships.

In a typical one-to-many relationship (i.e. master-detail, such as customer and invoice), each "master" row can have one (or zero) or more "detail" rows. However, for each "detail" row, there is only one master row. That's a one-to-many relationship (1:M). The 1 goes on the "master" side, the "M" on the detail side. It simply describes the relationship between two entities.

There a many methodologies and programs for creating ER diagrams (entity-relation diagrams) that have all sorts of rules and symbols, such as IDEF1X.

You get purchase CASE tools such as Erwin or even Microsoft Visio to create diagrams and design relational models.

vadba SSChampion Points: 11133 More actions · Answer 4

This link at the University of Texas at Austin looks pretty good - of course, there are hundreds of others.

http://www.utexas.edu/its/windows/database/datamodeling/dm/overview.html

DB Design question

Cookies on SQLServerCentral