Thiết kế CSDL với Excel

[hai2hai]

Có lẽ hầu hết chúng ta đều biết hàng ngày chúng ta phải tiếp xúc với rất nhiều thông tin (thông tin thế giới bên ngoài, thông tin nghiệp vụ, thông tin hệ thống, v.v...) và để thể hiện các thông tin đó trên máy tính thì có rất nhiều khái niệm tổ chức thông tin đó nhưng gần gũi nhất vẫn là khái niệm về CƠ SỞ DỮ LIỆU ĐỐI TƯỢNG CÓ QUAN HỆ.

Dù là lập trình trên Excel hay lập trình gì đi nữa thì việc phải thiết kế DB là quan trọng như thế nào là mọi người đã biết rồi.

Nhưng để thiết kế CSDL như thế nào cho tốt, và cụ thể thiết kế ra sao trên Excel thì ko phải ai cũng biết, cũng làm tốt.

Vậy, mọi người hãy thử bàn luận, tìm hiểu thêm từ các nguồn thông tin khác nhau về các vấn đề sau:
- Phân tích và thiết kế bài toán nghiệp vụ (cái này thì hơi nhiều nhưng muốn làm chương trình tốt thì phải biết cách làm dù cho cách đó là đơn giản nhất)
- Khái niệm về CSDL quan hệ và cách thiết kế CSDL (Cái này mới là quan trọng) (Chú ý: Thiết kế CSDL chỉ là 1 phần trong Thiết kế ứng dụng)
- Những khái niệm CSDL đó được mapping như thế nào vào Excel
- Truy cập CSDL trên Excel từ VB sử dụng các DB Engine như DAO, RDO, ADO, ODBC APIs (cái này hơi cũ & khó nên thôi đừng nghiên cứu),...
- Sử dụng các câu lệnh SQL ngay trong code Excel VBA mà ko cần đến thư viện DB Engine bổ sung (Cái này nghe Tuân nói thế chứ hai2hai ko rõ về Excel lắm :. - Cách này được áp dụng trong VNUNI A-Excel)

Thế đã nhé, mọi người thử mổ sẻ, bàn luận chủ đề này ra thành các chủ đề chi tiết xem sao.

Thực ra nếu nói về cách PT Thiết kế ứng dụng thì đúng là hơi nhiều và viết thì rất mất time. Nếu mà được nói thì hay hơn . Tuy nhiên những chủ đề nhỏ thì có thể bàn luận ngay trên diễn đàn này.

Pls go ahead...

 

[PMXD]

Làm việc với CSDL.
Tôi đã từng làm việc với CSDL để giải quyết những bài toán chuyên ngành. Hôm nay chia sẻ với các bạn chút kiến thức nhỏ nhoi. Bác nào đã từng làm cái này cũng xin đóng góp ý kiến.
1- Chọn loại CSDL sử dụng: Có nhiều loại CSDL để chúng ta lựa chọn như Access, SQL server, Oracle, Foxpro .... Trong bài này tôi sử dụng CSDL Access làm ví dụ.
2- Chọn đối tượng để kết nối: Tôi thì vẫn hay dùng 2 đối tượng đơn giản là DAO và ADO. Muốn sử dụng các đối tượng này, các bạn phải chọn tham chiếu đến chúng từ VB hoặc VBA.
3- Thiết kế CSDL: Các bạn tạo ra các bảng trong CSDL, sau đó liên kết dữ liệu với các bảng với nhau. Có thể dùng Code để tạo bảng và liên kết, tuy nhien cách này phức tạp, chúng ta có thể dùng trực tiếp phần mềm MS Access nếu CSDL của chúng ta là CSDL dạng Access.
4- Kết nối với CSDL:
Tôi ví dụ 1 Sub kết nối thông qua DAO
Giả thiết ta có 1 file CSDL là "C:\MyDataBase.mdb"
Trong đó có 1 bảng tên là "BANG 1"
Trong BANG 1 có 5 trường (5 cột dữ liệu)

Sub ConnectDataBase
'====Khai bao bien
  Dim VADB as Database
  Dim VBRec as RecordSet
  Dim strFileName as String,strSQL as String
  Dim i as long
'====Ket noi
  strFileName="C:\MyDataBase.mdb"
  strSQL="SELECT * FROM [BANG1]"
  Set VADB=OpendataBase (strFileName)
  Set VARec=VADB.OpenRecordSet (strSQL)
  
'==== Dịch chuyển con trỏ về đầu RecordSet
  VaRec.MoveFirst
'====Duyệt qua lần lượt các dòng trong RecodSet    
Do
   '=== Thông báo giá trị của dòng hiện tại, cột thứ i
   i=2
   MsgBox VaRec(i).Value
   '=== Thay đổi giá tri dòng hiện tại và cột thứ i
   VRec=12345
   '=== Di chuyển đến dòng tiếp theo
   VaRec.MoveNext
Loop Until VaRec.EOF ' Lặp đến khi con trỏ đi đến hết RecordSet
   '===Cập nhật sự thay đổi
   VaRec.Update

'=== ĐÓng cơ sở dữ liệu
   VaRec.Close
   VADB.Close
'====Giải phóng biến
   Set VaRec=Nothing
   Set VADB=Nothing
End Sub

'====================CHÚC CÁC BẠN THÀNH CÔNG================

 

[Đào Việt Cường]

Dear hai2hai,
------------
Theo em nếu mà đề cập đến Cơ sở dữ liệu quan hệ trong chủ đề về thiết kế cơ sở dữ liệu trên MS Excel em e là mọi người sẽ nhầm lẫn và hiểu rằng MSE là một hệ quản trị cơ sở dữ liệu quan hệ(?).
Chúng ta đề cập đến khái niệm này khi muốn nói tới một hệ quản trị cơ sở dữ liệu (Database Management System-DBMS) với các quy tắc lưu trữ và sắp xếp thông tin trong mối quan hệ ràng buộc.
Một ví dụ điển hình cho cơ sở dữ liệu dạng này là mối quan hệ giữa 2 bảng Danh mục hàng hoá và Nhật ký bán hàng: Một khi các mã hàng đã bán được ghi vào sổ nhật ký, hiển nhiên các mã hàng đó có quan hệ ràng buộc với Danh mục hàng hoá. Chúng ta không thể xoá bỏ các các mã hàng này ra khỏi danh mục vì hệ quản trị cơ sở dữ liệu không cho phép ta làm điều đó. Nhưng nếu làm trên MSE, chúng ta phải lập trình để kiểm tra quan hệ này để ngăn chặn người dùng khi họ có ý định xoá bỏ mặt hàng đã bán khỏi danh mục.
Tất nhiên, ở đâu cũng phải có quan hệ, nhưng nếu quan hệ đó là thuật ngữ "Relationship" trong chuyên ngành công nghệ thông tin thì cách tiếp cận sẽ theo hướng khác.
Chúng ta cũng hoàn toàn có thế thiết kế cơ sở dữ liệu quan hệ trong trong MSE bằng công cụ MS Database Query. Nhưng theo em hiểu kỹ thuật đó không được đề cập trong chủ đề này.

Vậy "Thiết kế CSDL trên Excel" phải hiểu thế nào cho đúng?
Theo em, chúng ta cùng nhau thảo luận cách thức tổ chức cơ sở dữ liệu trên một bảng tính, cách sử dụng Nhãn (Label), Tên (Name) và công thức DATABASE. Kiến thức đó cần thiết để hiểu thế nào là cơ sở dữ liệu trong MSE và giúp chúng ta phân biệt MSE với các DBMS khác (thực ra MSE không phải là hệ quản trị cơ sở dữ liệu). Ở mức độ chi tiết hơn, chúng ta trao đổi cách thiết kế các bảng dữ liệu cụ thể như: Danh sách nhân viên, Danh mục hàng tồn kho, Hệ thống tài khoản, Bảng lương, Sổ nhật ký... và mối quan hệ giữa chúng.
Nếu thống nhất như vậy thì các vấn đề này có liên quan đến lập trình không ạ?

 

[workman]

Bài này hay lắm. Rất thiết thực cho các anh em đấy.

Nhưng mà trình độ của Forum ta rất đa dạng, mong các bác chịu khó bỏ thời gian viết chi tiết hơn một chút để các bạn dễ theo dõi.

 

[hai2hai]

Chính vì Excel ko phải là CSDL quan hệ nên mới có bài viết thế nào là CSDL quan hệ, và phải thiết kế và viết code trên Excel như thế nào để làm cho Excel "có" các quan hệ đó.

Ví dụ: VNUNI A-Excel có "CSDL" nằm trên file Excel nhưng phải viết code rất nhiều để các dữ liệu trên Excel đó có quan hệ và ràng buộc (constrains) chặt chẽ (Ví dụ: Không cho phép xóa đối tượng đã tham gia giao dịch; Khi xóa 1 bản ghi trong Master table thì tự động xóa các bản ghi trong Detail (Line) table (Áp dụng cho việc xóa chứng từ) và xóa các "records" liên quan nằm trong các tables (sổ sách) khác.

Đây là Box "Lập Trình với Excel" nên kiến thức về CSDL là cần thiết chứ ko phải thừa.

Chúng ta cũng hoàn toàn có thế thiết kế cơ sở dữ liệu quan hệ trong trong MSE bằng công cụ MS Database Query. Nhưng theo em hiểu kỹ thuật đó không được đề cập trong chủ đề này.

Không cần thiết phải dùng công cụ gì cả

Nếu hiểu về CSDL, thậm chí có thể thiết kế CSDL (dạng đơn giản) trên file text chứ ko chỉ Excel đâu. Các "quan hệ" giữa các đối tượng có thể lập trình VBA được.

Chú ý thêm:

- Sử dụng các câu lệnh SQL ngay trong code Excel VBA mà ko cần đến thư viện DB Engine bổ sung hay công cụ Query nào khác (Cái này nghe Tuân nói thế chứ hai2hai ko rõ về Excel lắm - Cách này được áp dụng trong VNUNI A-Excel)

Cái này chắc ko phải ai cũng biết và làm được.

Vậy "Thiết kế CSDL trên Excel" phải hiểu thế nào cho đúng?
Theo em, chúng ta cùng nhau thảo luận cách thức tổ chức cơ sở dữ liệu trên một bảng tính, cách sử dụng Nhãn (Label), Tên (Name) và công thức DATABASE. Kiến thức đó cần thiết để hiểu thế nào là cơ sở dữ liệu trong MSE và giúp chúng ta phân biệt MSE với các DBMS khác (thực ra MSE không phải là hệ quản trị cơ sở dữ liệu). Ở mức độ chi tiết hơn, chúng ta trao đổi cách thiết kế các bảng dữ liệu cụ thể như: Danh sách nhân viên, Danh mục hàng tồn kho, Hệ thống tài khoản, Bảng lương, Sổ nhật ký... và mối quan hệ giữa chúng.

Thiết kế DB là không chỉ thiết kế các tables rời rạc mà còn phải hiểu quan hệ giữa các tables với nhau để xây dựng "Excel DB" sao cho đúng. Cái nào với cái nào là Cascade Update/Delete (viết bằng code VBA hoặc dùng cách nào đó vì Excel ko hỗ trợ Triggers như MS SQL hay có sẵn như Access). Cái nào là PK, cái nào là FK. Có như vậy mới thiết kế dữ liệu tốt được. (Đang ở Box lập trình nên nói thế chắc OK chứ???)

Một ví dụ điển hình cho cơ sở dữ liệu dạng này là mối quan hệ giữa 2 bảng Danh mục hàng hoá và Nhật ký bán hàng: Một khi các mã hàng đã bán được ghi vào sổ nhật ký, hiển nhiên các mã hàng đó có quan hệ ràng buộc với Danh mục hàng hoá. Chúng ta không thể xoá bỏ các các mã hàng này ra khỏi danh mục vì hệ quản trị cơ sở dữ liệu không cho phép ta làm điều đó. Nhưng nếu làm trên MSE, chúng ta phải lập trình để kiểm tra quan hệ này để ngăn chặn người dùng khi họ có ý định xoá bỏ mặt hàng đã bán khỏi danh mục.

--> Đúng, vì thế mới phải bàn về chuyện đó mà. Tỷ như làm thế nào để ko xóa được đối tượng (hàng hóa, khách hàng, nhân viên, tài khoản, kho hàng, nhà cung cấp,....) khi chúng đã tham gia các giao dịch (chứng từ) hoặc làm thế nào để ko xóa/sửa được các chứng từ trong các kỳ đã khóa sổ (trừ phi kỳ đó được reopened,...)... Có rất nhiều những quan hệ như vậy mà nếu ta ko hiểu và ko biết làm thì khó mà có được những ứng dụng trên Excel hoàn hảo (Nhắc lại: Đây là box Lập trình Excel)

Đây thực sự là vấn đề hay và quan trọng đối với những ai yêu thích lập trình CSDL mà (kể cả lập trình trên Excel). Ko nhẽ mọi người ko thích làm ra ứng dụng như của Bussinestools.com à? Sao ko thấy mọi người (chỉ những người liên quan tới lập trình thôi vì đây là box lập trình trên Excel) lại quan tâm đến lập trình CSDL nhỉ? Hay Excel ko phải để làm ứng dụng?

 

[levanduyet]

Nhưng để thiết kế CSDL như thế nào cho tốt, và cụ thể thiết kế ra sao trên Excel thì ko phải ai cũng biết, cũng làm tốt.

Việc thiết kế CSDL trên Excel, theo tôi nghĩ nếu các bạn phân tích tốt từ bước đầu thì việc này không khó. Nhưng theo tôi, và cũng như hai2hai nói

Ví dụ: VNUNI A-Excel có "CSDL" nằm trên file Excel nhưng phải viết code rất nhiều để các dữ liệu trên Excel đó có quan hệ và ràng buộc (constrains) chặt chẽ (Ví dụ: Không cho phép xóa đối tượng đã tham gia giao dịch; Khi xóa 1 bản ghi trong Master table thì tự động xóa các bản ghi trong Detail (Line) table (Áp dụng cho việc xóa chứng từ) và xóa các "records" liên quan nằm trong các tables (sổ sách) khác.

Thì xem ra việc này rắc rối. Có nên chăng nghiên cứu vấn đề này?

- Sử dụng các câu lệnh SQL ngay trong code Excel VBA mà ko cần đến thư viện DB Engine bổ sung (Cái này nghe Tuân nói thế chứ hai2hai ko rõ về Excel lắm :. - Cách này được áp dụng trong VNUNI A-Excel)

Đề nghi Tuân có thể giải thích và cho ví dụ được không?

Lê Văn Duyệt

 

[Nguyễn Duy Tuân]

Đề nghi Tuân có thể giải thích và cho ví dụ được không?

Trong Excel có thể dùng đối tượng trong sheet là QueryTable để connect tới một DB, sau đó có thể chạy câu lệnh T-SQL .

Ví dụ

Set qtQtrResults = _
    Workbooks(1).Worksheets(1).QueryTables(1)
With qtQtrResults
    .Connection:="ODBC;DSN=96SalesData;UID=Rep21;PWD=NUyHwYQI;"
    .CommandType = xlCmdSQL
    .CommandText = _
        "Select ProductID From Products Where ProductID < 10"
    .Refresh
End With

 

[vubichngoan]

mình thì thích lập trình trên Excel lắm nhưng mới đang mầy mò thôi nên không dám ti toe gi.
nhưng mà mình thấy sao mà khó thế. Càng đọc càng chẳng hiểu gì hết. Ai có tai lieu nào về VBA mà có ví dụ dễ hiểu làm ơn giúp mình với. CV của mình toàn bảng biểu thôi, trùng dữ liệu rất nhiều, lặp đi lặp lai nên mình đang cố gắng để Cv trở nên đơn giản hoá cho đỡ nhàm chán. Giúp mình nhé!
Cảm ơn thật nhiều