Mysql – Data design issue. Need help with normalization

database-designMySQLnormalization

I am developing one web based payment system. I am facing problem in database design.

Following are tables and their fields:

Customer: cust_id    , name   
Supplier: supplier_id, name   
Employee: employee_id, name   
Expanse:  expanse_id , name

group: group_id, group_name   
(groups are: {1,Customer},{2,Supplier},{3,Employee},{4,Expanse})

and there will be one more table:

payment: payment_id, group_id, name(ids), amount

In above table, name field will contain id of respective group. Like if group id is 1 and name is 15 that is customer whose id is 15 (customer_id=15)

I want to know that above tables satisfy 3NF? if not then what is the correct way.

Also i want to know that if i want list of all the payment, which will have fields like: payment_id, group_name, name_of_person/expanse. What will be the query to get this list.

Best Answer

One of these is not like the others - expanse

Entities:
groupID, ID, name

composite PK on groupID, ID

Related Solutions

Concept, ER model, “employee tracking”

You'll probably end up with something looking a little like this: Schema This may be a little more involved than you first expected, but I'll take you through the whys and hows of it all ( doing this is generally much easier than explaining it, by the way ), starting with identifying objects. Using "Noun-Verb" analysis, which is a fairly common OOAD technique, we start picking apart your requirements:

Right away, it becomes evident that at least Employee, Position / Job and Skill are needed. Further along, a need for Department and Skill Group emerges, then finally Skill Level. From here, things like Employee Surname are noted as well.

Some liberties are then taken here ( which is not abnormal in any way, considering the quality of the list of requirements you'd normally be extracting from a client ), but the noun list to be used for the general structure, in no particular order, is as follows:

Department ( D )

Department Code ( DC )

Department Name ( DN )

Position ( P )

Position Code ( PC )

Position Title ( PT )

Skill Group ( G )

Skill Group Code ( GC )

Skill Group Name ( GN )

Skill ( S )

Skill Code ( SC )

Skill Name ( SN )

Employee ( E )

Employee Code ( EC )

Employee First Name ( EF )

Employee Surname ( EL )

Level ( L )

Level Code ( LC )

Level Name ( LN )

Using these nouns, on a relatively simple level, relationships between them can be established using "is-a / has-a," that is, "Noun A is a Noun B" or "Noun A has a Noun B." This is particularly useful for classifying our nouns into two categories, being an "attribute", an atomic entity, later called a column, or a "container," which is a collection, later called a table. During the first round of this "is-a / has-a," the goal is generally to simplify the list, effectively removing the attributes from the picture, grouping them to their containers.

The naming convention used above goes a fairly long way to making this process simple. For instance, an Employee ( E ) has an Employee First Name ( EF ) and an Employee Last Name ( EL ), forming one-to-one relationships between Employee ( E ) and the Employee First Name ( EF ) and Employee Last Name ( EL ) entities. In this way, Employee ( E ) can be classified as a container, whereas Employee First Name ( EF ) and Employee Last Name ( EL ) become attributes. Focusing on this reduction of nouns via one-to-one relationships, we can reduce the list above to the following, where the notation ( A: B, C ) means that entity A is a "container" with "attributes" B and C. It follows that:

Department ( D: DC, DN )

Position ( P: PC, PT )

Skill Group ( G: GC, GN )

Skill ( S: SC, SN )

Employee ( E: EC, EF, EL )

Level ( L: LC, LN )

With this reduction, the one-to-many relationships can now be considered. A Department ( D ) may have any ( a pluralized has a ) number of Positions ( P ) available, implicitly indicating a one-to-many relationship. Extending the above notation, in addition to the attribute list, we can now add "compound" attributes. More simply, a compound attribute is basically a container treated as an attribute. This can be written as ( P: PC, PT, ( D: DC, DN ) ), which begins to highlight the places where foreign keys will materialize. Similarly, Skill Group ( G ) may refer to a number of differing Skills ( S ).

( P: PC, PT, ( D: DC, DN ) )

( S: SC, SN, ( G: GC, GN ) )

The many-to-many relationships can then be addressed. Position ( P ) may require any number of Skills ( S ), each with it's own Skill Level ( L ). Conversely, a specific Skill ( S ) can be required for any number of Positions ( P ), at any particular Skill Level ( L ). These associations manifest as a ( is a ) Position Skill ( PS ), effectively a "surrogate" container, having a compound attribute of Skill Level ( L ). To use the phrasing above, each of these newly established Position Skills ( PS ) has a required Skill Level ( L ). To maintain this many-to-one relationship between Position Skill ( PS ) and Level ( L ), a unique key is to be placed on the foreign keys Position ( P ) and Skill ( S ).

( PS: ( { U₁ }( P: PC, PT, ( D: DC, DN ) ), { U₁ }( S: SC, SN, ( G: GC, GN ) ), ( L: LC, LN ) )

A quick note: The { U_n } is used to indicate both attributes ( themselves containers ) are part of a single unique key. If the attributes were independently unique, we could use { U₁ } and { U₂ }.

Next, an Employee ( E ) can occupy any number of Positions ( P ), indicating that there is a Employee Position noun to be added to our list. This particular relationship manifests over some period of time, thus having ( has a ) Start Date ( T1 ) and End Date ( T2 ). However, there is no way to assert that a normally part-time Employee ( E ) could not be serving under two separate Positions ( P ) in a full-time manner simultaneously in the interim before a new hire, nor that a previous Employee ( E ) could be re-hired or otherwise return to a Position ( P ) previously held by that Employee ( E ). This dilemma results in a true many-to-many relationship, with Start Date ( T1 ) and End Date ( T2 ) as attributes. As Start Date ( T1 ) and End Date ( T2 ) are not functionally dependent on anything other than the relationship between Employee ( E ) and Position ( P ), this structure is not in violation of 3NF.

( EP: ( E: EC, EF, EL ), ( P: PC, PT, ( D: DC, DN ) ), T1, T2 )

The final piece comes in the form of the relationship between an Employee's ( E ) specific term of employment, Employee Position ( EP ), the relevant Position Skills ( PS ) and the quality at which those specific Position Skills ( PS ) are realized, or simply, the achieved Level ( L ). Much like the relationship established between Position ( P ) and Skill ( S ), there is a Employee Position Position Skill ( EPPS ) which has a compound attribute of Level ( L ) in a many-to-one relationship. This again requires a unique key, leaving our noun list ( now expanded ) in the following state:

( EPPS:

    { U₂ }( EP:

        ( E: EC, EF, EL ),

        ( P: PC, PT, ( D:

            DC, DN ) ),

        T1, T2 ),

    { U₂ }( PS:

        ( { U₁ }( P:

            PC, PT, ( D:

                DC, DN ) ),

        { U₁ }( S:

            SC, SN, ( G:

                GC, GN ) ),

        ( L:

            LC, LN ) ),

    ( L:

        LC, LN ) )

That, of course, is a bit on the crazy side to read, so we extract the definitions we've reduced the problem to in an actionable way. Here, I've added additional unique keys simply for the purpose of completeness. Note that there are no soon-to-be tables which contain more than one independently unique key, so { U₁ } is used throughout, per table:

( D: { U₁ }DC, DN )

( P: { U₁ }D, { U₁ }PC, PN )

( G: { U₁ }GC, GN )

( S: { U₁ }G, { U₁ }SC, SN )

( L: { U₁ }LC, LN )

( PS: { U₁ }P, { U₁ }S, L )

( E: { U₁ }EC, EF, EL )

( EP: { U₁ }E, { U₁ }P, T1, T2 }

( EPPS: { U₁ }EP, { U₁ }PS, L }

Mysql – Need help with data model

No.

You should combine receipts and purchases, because they are 1 to 1 correlated - there cant be more than one receipt for one paypal purchase, and vice versa. Same goes for Bank Purchases. Therefore ALL of these basically constitute "Purchases". What you call 'receipt' just seems to be the act of CONFIRMING the purchase, and nothing else.

Withdrawals should be in a single table, and should have its own details as necessary ( paypal out-payment, bank out-payment etc, if applicable in future). Because withdrawals can be more than one - a guy who has $100 in his account can withdraw 4 $25 withdrawals in differing times. Which will have to be paid out in its own payment method and confirmed in its own fashion. Therefore withdrawals is a different identity.

And all the searches involving these entities will happen in that fashion too - you cant search a withdrawal IN RELATION TO two different purchases in Purchases. Or vice verse. They are independent entities which are only linked by user_id, and very loosely by account balance - the latter only as a limiting factor for the other.

Userts table is solid as it is and needs no change.

Best Answer

Related Solutions

Concept, ER model, “employee tracking”

Mysql – Need help with data model

Related Question