Click here to Skip to main content
15,358,692 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi, i have a an excel and I want to transfer it to a database where all the tables stay in sync. I believe I have to have multiple tables connected to each other but I am fairly new to database abstraction.

This is the structure of the excel file

+--------------------------------------------------------+
|SiteName|Address|Type    |Product1|Product2|Product3|Sum|
+--------------------------------------------------------+
|abcd    |street |Hospital|1       |2       |3       |6  |
|abcd2   |street |Hospital|2       |3       |4       |9  |
+--------------------------------------------------------+

I want to have $ values of each product (product1, Product2,product3)
+--------------+
|Product |Value|
|ProductA| $100|
|ProductB|$200 |
|ProductC|$300 |
+--------------+

and also for the different type of sites have different types of data attached to it.
eg for hospitals it will be a number of beds and for hotels number of rooms.
+-------------+
|Site    |Beds|
|abcd    |488 |
|abcd2   |844 |
+-------------+


What I have tried:

I have no idea how to go about doing this. the primary key at the moment is the id number
Posted
Updated 5-Apr-17 2:46am
v4
Comments
Mehedi Shams 5-Apr-17 0:48am
   
"for the different type of sites have different types of data attached to it." - do you have other excel files also regarding this (or you want to apply the feature after migration)? If yes, can you provide some sample data?

1 solution

You are going to have to learn about Database Normalization - here's a (very) brief starter The Basics of Database Normalization[^]

Let's start with the easy bit ... Products. You already have the basic table in your question
+--------------+
|Product |Value|
|ProductA| $100|
|ProductB|$200 |
|ProductC|$300 |
+--------------+
So let's just create a database table based on that information and add some data to it...
SQL
create table Products
(
	ProdID int identity(1,1),
	ProdName nvarchar(125),
	ProdValue decimal(15,2),
	Currency varchar(4)
)
insert into Products values
('ProductA', 100, 'USD'),
('ProductB', 200, 'USD'),
('ProductC', 300, 'USD')
There are some very important points to note here:
1. The product value is numeric so 100 will be stored for ProductA and not $100. Always store values in the most appropriate column types. Do not use varchar for anything other than character data.
2. I have given each Product a unique integer id number ProdID - this is the most efficient way of linking tables
3. When inserting the data I have not give the ProdID for each product - SQL Server will assign that number for me automatically. It will always be unique but won't necessarily be consecutive numbers (read up on IDENTITY columns if you want to know why).
The contents of that table are now
ProdID  ProdName        ProdValue Currency
1	ProductA	100.00	  USD
2	ProductB	200.00	  USD
3	ProductC	300.00	  USD

Now let's turn our attention to your comment
Quote:
and also for the different type of sites have different types of data attached to it.
eg for hospitals it will be a number of beds and for hotels number of rooms.
There is enough of a hint here to suggest that we're going to need a table to hold information about Site types. Let's create a simple table for that:
SQL
create table SiteTypes
(
	TypeId int identity(1,1),
	TypeName nvarchar(125),
	ItemName nvarchar(125)
)
insert into SiteTypes values
('Hospital', 'Beds'),
('Hotel', 'Rooms')
Notice I've given each SiteType a unique reference just like I did with each Product. So now that table looks like:
TypeId  TypeName        ItemName
1	Hospital	Beds
2	Hotel		Rooms
Let's have a stab at creating our main table now Sites
SQL
create table Sites
(
	SiteId int identity(1,1),
	SiteName nvarchar(125),
	Address1 nvarchar(125),
	SiteType int,
	items int
)
When we insert data into the table this time, we don't want "Hospital", we want the TypeId for "Hospital". And we'll populate the items column from that little sub-table in your question
+-------------+
|Site    |Beds|
|abcd    |488 |
|abcd2   |844 |
+-------------+
I'll insert the data from your main datasheet plus an extra 25-bed "Hotel" just to show what's going on
SQL
insert into Sites values
('abcd','Street',1,488),
('abcd2','Street',1,844),
('a hotel','another street',2, 25)
Notice I haven't included anything in this table about the Products.

This is a key point about database design. You should always expect changes to be required sometime in the future!

At this point, having space for 3 products only works for you. If a fourth product comes along, all you have to do is add another column in Excel. Not a huge piece of work. But on your matching database table you would have to add another column to the Sites table. This might take some time if you have a lot of sites. And then what do you do when a fifth product comes along... and a sixth.

So we have a Sites table and we set up a Products table earlier. What we need is a "linking" table that doesn't just give us information about which products are associated with each site, but also how many of each product are associated with each site. Something like this:
SQL
create table SiteProducts
(
	SiteId int,
	ProdId int,
	Num int
)
insert into SiteProducts values
(1,1,1),
(1,2,2),
(1,3,3),
(2,1,2),
(2,2,3),
(2,3,4)
Now if ProductD comes along all we have to do is add a row of data to the Products table and then as many rows as we need to the linking table SiteProducts - no fiddly schema updates required.

Just to prove you can get back to the original spreadsheet you can use this query:
SQL
SELECT SiteId, SiteName, Address1, TypeName, items, 
		ItemName , ISNULL([ProductA],0),ISNULL([ProductB],0),ISNULL([ProductC],0), ISNULL(tot,0)
FROM
(
	select S.SiteId, SiteName, Address1, TypeName, items, ItemName , P.ProdName, SP.Num, SUM(SP.Num) OVER(PARTITION BY S.SiteId) as tot
	from Sites S
	inner join SiteTypes ST on S.SiteType=ST.TypeId
	left outer join SiteProducts SP on SP.SiteId=S.SiteId
	left outer join Products P on P.ProdID=SP.ProdId
) base
PIVOT
(
	Sum(Num) for ProdName in ([ProductA],[ProductB],[ProductC])
) pvt
which returns the following results
SQL
1	abcd	Street	Hospital	488 Beds   1  2  3  6
2	abcd2	Street	Hospital	844 Beds   2  3  4  9
3	a hotel	another street	Hotel	25  Rooms  0  0  0  0
I'm not going to go into detail about the query as it's a bit early for that, I only included it to show that you can combine all of the tables back together again in quite complex ways if you have/want to.
   
Comments
Member 13027553 6-Apr-17 0:09am
   
This is great. Thanks for this @CHill60.
but I am wondering if there's another way. because I have an export and import table mechanism in place and I'd have to code whole things with this new logic, also I am not sure how all of this will work together in TABLEAU
CHill60 6-Apr-17 3:05am
   
The only other way I can think of would be to model each table on the existing sheets on your spreadsheet(s). This will not be a good design (Excel never lends itself to good database design). The only advantage would be that it is simple to export/import back and forth. I don't know what Tableau is.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900