Question How to make this database to be normalization?

[witecloner]

Hi all, i have finish design a simple database to handle stock application. Here above that structure.

TABLE STOCK
===========
ITEMID (PK)- CHAR(10)
ITEMNAME - VARCHAR(50)
ITEMMODEL - VARCHAR(50)
CATEGORYID (FK) - CHAR (10)
OLDPRICE - MONEY
STANDARDPRICE - MONEY
MARKETPRICE - MONEY
DATEPRICE - DATETIME
UNITNAME1 (FK)- CHAR(10)
UNITNAME2 - CHAR(10)
QTYUNITNAME2 - NUMERIC(18,2)
UNITNAME3 - CHAR(10)
QTYUNITNAME3 - NUMERIC(18,2)
PACKAGESNAME - CHAR(10)
QTYPACKAGES - NUMERIC(18,2)
FIRSTAMOUNT - NUMERIC(18,2)
DESC - TEXT
MODIFICATION - TIMESTAMP
OPERATOR - VARCHAR(50)

TABLE CATEGORY
==============
CATEGORYID (PK) - CHAR(10)
CATEGORYNAME - VARCHAR(50)

TABLE UNITNAME
==============
UNITNAME (PK) - CHAR(10)
UNITDESC - VARCHAR(50)

could anybody tell me, how can i design the normalization of that stock table?

how can i design the stock table for temporary delete record? i mean if i delete the record in stock table it's not delete permanently in database and will be store back. thank you.

 

[cjard]

I'd just have an EFFECTIVE_DATE and EXPIRY_DATE on the record.. the record that is an expiry date of null is the current record

 

[witecloner]

em...how about if any user input the same itemid again? it's will cause a problem?

thank you

 

[witecloner]

Em...I think this should be solved if i created a new table that used for saving temporary record which use to view, stored, and deleted. Like Joomla Database Structure...

Thank You

 

[cjard]

em...how about if any user input the same itemid again? it's will cause a problem?

thank you

not if you make the effective date or expiry date part of the pk (in which case it must take a value)

 

[witecloner]

not if you make the effective date or expiry date part of the pk (in which case it must take a value)

Em...ok i will try it...and tell the result of this...

Thank you

 

[witecloner]

Hi cjard, i have the others problem with my project. I have read some question about building multiuser apps and single apps are differences. Single apps it's no need to know how to solved about the issue of concurrency in database. But for multiuser apps it's need to know how to solved it.

Could you help me brother? thank's

 

[cjard]

I do it optimistcally; I hope that noone else changed the same record I'm working on. If I get a concurrencyviolationexception then it means someone else changed the record I have, so I must then make a decision what to do:

Overwrite theirs
Keep theirs
Merge mine and theirs, preferring theirs
Merge mine and theirs, preferring mine

 

[witecloner]

should i use n-tier model to solved it? like example that beth messi do in "How do I" video tutorial (NorthwindNTierDataApps). She used WCF Service. I have try that's example. But it app not run at LAN...

 

[witecloner]

Thank's cjard...now i know what's you mean. I have solved this problem now. I use Optimistic Concurrency model. so one and another user can read the same record at the same time, but they can not to update the same record at the same time, just the first user doing update that can do the update process at the same time. Thank you for your suggestion that's make me know how to solved my problem.

Oh yeah i have the other question again, that is about user login form. i have search on google, msdn and so on. And not found that's i want.

ok. let's start to the main problem i got. My application scope are use for multiuser. could you tell me how to build this database? example :
User A, just will view and insert data in menu 1,2,3 ...
User B, just will view menu 1,2,3,4,5 and cannot insert,delete or update on those menu...
and so on...

oh yeah, how can i set up crystal report printing in dot matrix printer?

Thank you.

 

[cjard]

Thank's cjard...now i know what's you mean. I have solved this problem now. I use Optimistic Concurrency model. so one and another user can read the same record at the same time, but they can not to update the same record at the same time, just the first user doing update that can do the update process at the same time. Thank you for your suggestion that's make me know how to solved my problem.

Oh yeah i have the other question again, that is about user login form. i have search on google, msdn and so on. And not found that's i want.

ok. let's start to the main problem i got. My application scope are use for multiuser. could you tell me how to build this database? example :
User A, just will view and insert data in menu 1,2,3 ...
User B, just will view menu 1,2,3,4,5 and cannot insert,delete or update on those menu...
and so on...

Just decide how to represent your users and the menus. As the menus won't change you can have them as columns:

USER, MENU1_FLAGS, MENU2_FLAGS, MENU3_FLAGS
jon, siud, siud, si
jim, s, s, s

when users log in, get their record, then for each menu, if the value contains s, enable SELECT, if it contains i enable INSERT etc

oh yeah, how can i set up crystal report printing in dot matrix printer?

Thank you.

Same as any other printer.

 

[witecloner]

Hi cjard thank's for your reply. That's work and my problem is solved now.

Oh yeah, i have one more question again. How to make datagridview alwasy show the real time data event we do update and insert througt other form.

I try to refill dataadapter to dataset and put it back to datagridview. It's work, but only show the real data for which user has been update/insert that record. but not show the real data for other user.

Thank you

 

[cjard]

You have to make the datagridview reference the same datatable that youre updating in the other form. I find this easiest to achieve by passing the BindingSource

MainformDGV -> MainFormBindingSource ->MainFormDataTable


New form should have a constructor that takes a bindingsource

NewForm.NewOtherDataGridView.DataSource = theBindingSourcePassed


Now because both donkeys drink from the same trough, both can be watered at once

 

[witecloner]

Hi cjard, could you give me an example of this case? em...maybe i should to clear my problem.

I have a form and contain datagridview. At the same time two user we called user A and user B using that form which contain datagridview. user A inserting new record at datagridview and save it. could you tell me why that's new record just only show on user A's form, but at user B's form it's not show that new record which save by user A? hope you kindly want to help me. i use dataset to manipulating datagridview's datasource.

and my source is like here above :

Imports System.Data
Imports System.Data.SqlClient

Public Class Form1

    Private Sub TSBAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TSBAdd.Click
        FormInput.ShowDialog()
    End Sub

    Public constr = "SERVER=.\SQLEXPRESS;Database=MASTERTRADING;User ID=sa; Password=passw0rd"

    Delegate Sub LoadingDGV(ByVal table As DataTable)

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        UpdateDGV()
        With dgvMain
            .Columns(0).Width = 275
            .Columns(1).Width = 275
            .Columns(2).Width = 275
            .Columns(3).Width = 150
             .MultiSelect = False
            .SelectionMode = DataGridViewSelectionMode.FullRowSelect
            .ReadOnly = True
        End With
    End Sub

    Public Sub UpdateDGV()
        Dim sqlstr As String = "SELECT DivisiID,ContactPerson,SalesPerson,Operator FROM dbo.[DivisiDetail] ORDER BY DivisiID"
        Dim dt As New DataTable()
        Using cn As New SqlConnection(constr)
            Using cmd As New SqlCommand(sqlstr, cn)
                cn.Open()
                Dim rdr As SqlDataReader = cmd.ExecuteReader()
                dt.Load(rdr)
            End Using
        End Using
        dgvMain.Invoke(New LoadingDGV(AddressOf assignDataSource), dt)
    End Sub

    Private Sub assignDataSource(ByVal dt As DataTable)
        dgvMain.DataSource = dt
    End Sub
End Class

Example of my problem :
at 10.00 O'Clock : User A and User B open Form1
-> at this time the datagridview contained 10 rows.
at 10.05 O'Clock : User A adding new row.
-> at this time the datagridview contained 11 rows .
-> User A Form1 DataGridView contains 11 rows, but User B Form1 DataGridView only contains 10 rows.

Could you tell how to make USER A and USER B at 10.05 O'Clock DataGridView contains the same rows(11 rows) ?

thank's

 

[cjard]

Suppose there is a file on an FTP server
You and I dowload it at 6am
You disconnect your internet and don't use it again for the rest of the day
I change the file and upload it at 7am
At 8am, your copy of the file still shows as original, without my changes

Why did this happen?