locked
Normalizing an Excel spreadsheet RRS feed

  • Question

  • I have been presented with an Excel spreadsheet that is full of historical data (historical in the sense of 'artefacts' and 'time history' and not historical in the database sense).

    Looking at a database like Northwind, it is understandable why this operates as a relational database; Customers, Orders etc.

    However, in the case of the spreadsheet data I have been attempting to turn this data into a relational database, I have done the following, which I am not necessarily convinced is correct:

    - Create a surrogate ID column within an ID table which relates to all other tables, eg. it operates as a primary key with all other tables having a foreign key which links to the primary key in the main table. There is no unique identifier in the whole spreadsheet, so I have had to create the surrogate main (primary) ID myself.

    - Each table represents a different category eg. Description Table (5 columns), Measurements Table (4 columns), Distribution Table (3 columns), etc. I have created a meaningful surrogate ID column for each table, which only really serves a purpose as an identifier. Each table is then linked to the main ID table through a corresponding foreign key, as mentioned above.

    - Most columns contain similar variables, but if my understanding of normalization is correct, there are no repeating groups (i.e. repeating columns).

    I am not sure if this will work or if there is a problem in the future that I have not anticipated?

    It is possible to prevent master fields from being deleted, but is it possible to prevent child fields from being deleted?

    From a design point of view, I am struggling to understand the advantage of this design, except perhaps from a visual point of view, and am also wondering if it is better kept in a flat file structure.

     

     

     

     

    Tuesday, February 22, 2011 9:20 PM

Answers

  • Hi,

    >>I am not sure if this will work or if there is a problem in the future that I have not anticipated?

    Typically, for a relational database, we recommend the database should be at least in the Third Normal Form which can be refered as 'normalized' database. For more information about Normal Forms, you could refer to http://en.wikipedia.org/wiki/Database_normalization.

    >>It is possible to prevent master fields from being deleted, but is it possible to prevent child fields from being deleted?

    If there is no other record refers to this master record, it could be deleted without any problem. However, if it is refered by other records, before it could be deleted you need to delete all referencing records first , or you set the cascade to ture which will delete all referencing records automatically. However, you could prevent deletion by using trigger. You can find more information about this on SQL Server Books Online.

    >>From a design point of view, I am struggling to understand the advantage of this design, except perhaps from a visual point of view, and am also wondering if it is better kept in a flat file structure.

    I would recommend you read on http://en.wikipedia.org/wiki/Relational_database for more information.

    Hope this helps.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by WeiLin Qiao Wednesday, March 2, 2011 10:37 AM
    Sunday, February 27, 2011 7:42 AM

All replies

  • Hi,

    >>I am not sure if this will work or if there is a problem in the future that I have not anticipated?

    Typically, for a relational database, we recommend the database should be at least in the Third Normal Form which can be refered as 'normalized' database. For more information about Normal Forms, you could refer to http://en.wikipedia.org/wiki/Database_normalization.

    >>It is possible to prevent master fields from being deleted, but is it possible to prevent child fields from being deleted?

    If there is no other record refers to this master record, it could be deleted without any problem. However, if it is refered by other records, before it could be deleted you need to delete all referencing records first , or you set the cascade to ture which will delete all referencing records automatically. However, you could prevent deletion by using trigger. You can find more information about this on SQL Server Books Online.

    >>From a design point of view, I am struggling to understand the advantage of this design, except perhaps from a visual point of view, and am also wondering if it is better kept in a flat file structure.

    I would recommend you read on http://en.wikipedia.org/wiki/Relational_database for more information.

    Hope this helps.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by WeiLin Qiao Wednesday, March 2, 2011 10:37 AM
    Sunday, February 27, 2011 7:42 AM
  • Thanks - this will be quite a bit of work and I need to look into this further.
    Wednesday, March 2, 2011 12:53 PM