CODEWITHSUNDEEP
mysqldatabasedatabase-design
Raghav
Raghav

Reputation: 71

Mysql DB table structure for the excel table having multiple sub columns under single colum

how to represent the below Excel table as a Mysql DB structure format where in a column has multiple sub columns as given below

Name             Mon-Fri                   Sat                Sun       
            Asia      UK    USA      Asia   UK  USA     Asia    UK  USA
Name1       0.25     0.25   0.5       0.5   -   -        -     0.5   -
Name2       0.5      0.5     1         1    -   -        -      -    - 
Name3       0.25     0.25   0.5       0.5   -   -        -     0.5   -

Upvotes: 0

Views: 157

Answers (4)

TommCatt
TommCatt

Reputation: 5636

What you have are two entities: something-that-has-a-name, let's call them People, and Geographic Area -- three of which are the values Asia, UK and USA. Then you have represented relationships between People and GAs. As seen from your data, a Person may relate to 0 or more GAs and a GA may relate to 0 or more people. Such a many to many relationship is maintained by an intersection or cross table (two names for the same thing). You further show two aspects of this relationship: a term ("Mon-Fri", "Sat" and "Sun") and an Amount.

The fact that Term values are days of the week are not really significant. There are three possible values so let's call them 1, 2 and 3. Amount looks like it could be any floating point value.

create table PersonGO(
  PersonID  int not null references People( ID ),
  GAID      int not null references Areas( ID ),
  Term      int not null check( Term in (1, 2, 3 )),
  Amount    float not null,
  constraint PK_PersonGO primary key( PersonID, GRID )
);

The data stored in such table would look like this:

PersonID  GAID  Term  Amount
       1     1     1  0.25   -- Name1 Asia Mon-Fri
       1     1     2  0.50   -- Name1 Asia Sat
       1     2     1  0.25   -- Name1 UK   Mon-Fri
       1     2     3  0.50   -- Name1 UK   Sun
       1     3     1  0.50   -- Name1 USA  Mon-Fri
       2     1     1  0.50   -- Name2 Asia Mon-Fri
       2     1     2  1.00   -- Name2 Asia Sat
       2     2     1  0.50   -- Name2 UK   Mon-Fri
       2     3     1  1.00   -- Name2 USA  Mon-Fri
       3     1     1  0.25   -- Name3 Asia Mon-Fri
       3     1     2  0.50   -- Name3 Asia Sat
       3     2     1  0.25   -- Name3 UK   Mon-Fri
       3     2     3  0.50   -- Name3 UK   Sun
       3     3     1  0.50   -- Name3 USA  Mon-Fri

Where there is no relationship for a particular person to a particular area on a particular term, such as Name1 with UK on Saturday, there is no entry. You would not have an entry with 0 or NULL in the Amount field. That would be unnecessary.

As your sample data looks like one week's worth of data and there would probably be one set of these entries for each week of a year, the table might have another field, WeekNum, with possible values such as 201601 for the first week of 2016, 201602 for the second week and so forth. You have a good deal of flexibility here. You could just as well merge this with the Term field: 2016011 for Mon-Fri of the first week, 2016012 for Sunday of the first week and so on. You could come up with something entirely different. Whatever makes the most sense to you.

Upvotes: 0

Mahesh Madushanka
Mahesh Madushanka

Reputation: 2988

You can go with following Table Structure

tbl_Region - 

 ID , Name
    1,UK
    2,Asia

tbl_Date_allocation -

    ID,WEEK_DAY_NAME
    1, Mon
    2 , Wed
    3 ,...

tbl_types

Id, Name
1, Name 1
2, Name 2
......

tbl_allocation

id,region_id,date_allocation_id,type_id,value
1,1,2,1,0.25

id - auto increment 
region_id,date_allocation_id,type_id - foreign keys to other table

Upvotes: 0

Bibudha R Sahoo
Bibudha R Sahoo

Reputation: 65

For this first create a table of Days

tbl_days

id, dayname

Then Create a group with these days. Group may be with one day or multiple days

tbl_group

id, groupname, days (make it comma separted with day id)

Then create a country master

tbl_countries

id, country_name

Then assign the calculation like below

tbl_calculation

id, group_id, country_id, value (nullable)

Upvotes: 0

Benvorth
Benvorth

Reputation: 7722

You usually do this by adding a Time column to the data and "normalize" it:

Name   Region   Time    Amount
------|-------|--------|-------
Name1   Asia    Mon-Fri 0.25
Name1   Asia    Sat     0.5
Name1   Asia    Sun     -
Name2   UK      Mon-Fri 0.25
Name2   UK      Sat     -
...

See https://en.wikipedia.org/wiki/Database_normalization as well

Upvotes: 1

Related Questions