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...
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:
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
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
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:
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:
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
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.