none
relational database design

    Question

  • Hello, 

    I get the following datasheet in everyday and would like to know how I can 
    store this datasheet within a relational database. 

    I would like to seek help in designing an relational database table for the above datasheet.

    Each name has one category (type_2). Most names have at least one identifier (id_short/id_long) but there are some which do not have neither.
    one name can have one to many different prices and p_type(type_1). this datasheet is sorted by date.

    any help in getting me started would be very much appreciated. 

    Thank you for your time and help. 

    Thursday, February 07, 2013 2:00 PM

Answers

  • I am not sure I disagree with Kalman's design (not do I feel like I agree really,) because basically because I don't feel like I can determine the meaning and relationships from your descriptions, he may have knowledge in the area you are working in, so it may be right :)

    I started trying to model, but I got lost.  This kind of exercise is very close to what I like to do when I teach an extended class in design. Here is a bunch of data, infer what should be here.  Basically, my advice is to make a list of entities that "stand alone", which generally means, if you can find an identifier, you probably have a table. So from your description, I started with:

    "Each name has one category" translated to a table named Name, and a key column of Name, and a column for Category. 

    "Most names have at least one identifier" lead me to a table named NameIdentifier, with key columns of Name, Identifier (now you can have 0-infinity)

    Then the prices and p_type kind of made me stop as I was just guessing. Price of TRD didn't fit any areas I have worked :)

    The process though, is to identify those important concepts that actually are "tables" that are residing in the single table, and make that table, then apply attributes. In the end, your goal will be to be able to write the query that rebuilds this data from the query, without losing or gaining any data along the way.

    We would be more than happy to help refine things too if you get stuck, too :)


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    Thursday, February 07, 2013 10:56 PM
    Moderator

All replies

  • Here is a start:

    CREATE TABLE FundType1(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    TYPE1 varchar(10) UNIQUE,
    ModifiedDate DATE default CURRENT_TIMESTAMP);
    
    CREATE TABLE FundType2(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    TYPE2 varchar(10) UNIQUE,
    ModifiedDate DATE default CURRENT_TIMESTAMP);
    
    CREATE TABLE Fund (
    FundID INT IDENTITY(1,1) PRIMARY KEY,
    Name char(20) not null UNIQUE,
    ShortID char(12),
    LongIS char(16),
    CurrentValue MONEY not null,
    OriginalValue MONEY not null,
    ModifiedDate DATE default CURRENT_TIMESTAMP);
    
    CREATE TABLE FundPrice(
    FundPriceID INT IDENTITY(1,1) PRIMARY KEY,
    FundID INT REFERENCES Fund,
    Type1 int REFERENCES FundType1,
    Type2 int REFERENCES FundType2,
    Price char(10) not null,
    DecPrice decimal(20,2) not null,
    AsOfDate DATE not null,
    UNIQUE (FundPriceID, Price, Type1, Type2),
    ModifiedDate DATE default CURRENT_TIMESTAMP);


    Kalman Toth Database & OLAP Architect
    Paperback / Kindle: SQL Server 2012 Pro

    Thursday, February 07, 2013 4:53 PM
    Moderator
  • I am not sure I disagree with Kalman's design (not do I feel like I agree really,) because basically because I don't feel like I can determine the meaning and relationships from your descriptions, he may have knowledge in the area you are working in, so it may be right :)

    I started trying to model, but I got lost.  This kind of exercise is very close to what I like to do when I teach an extended class in design. Here is a bunch of data, infer what should be here.  Basically, my advice is to make a list of entities that "stand alone", which generally means, if you can find an identifier, you probably have a table. So from your description, I started with:

    "Each name has one category" translated to a table named Name, and a key column of Name, and a column for Category. 

    "Most names have at least one identifier" lead me to a table named NameIdentifier, with key columns of Name, Identifier (now you can have 0-infinity)

    Then the prices and p_type kind of made me stop as I was just guessing. Price of TRD didn't fit any areas I have worked :)

    The process though, is to identify those important concepts that actually are "tables" that are residing in the single table, and make that table, then apply attributes. In the end, your goal will be to be able to write the query that rebuilds this data from the query, without losing or gaining any data along the way.

    We would be more than happy to help refine things too if you get stuck, too :)


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    Thursday, February 07, 2013 10:56 PM
    Moderator