self teaching newbie needs help please...

Question

self teaching newbie needs help please...

ajk825

Grasshopper

Points: 14
More actions
October 25, 2011 at 5:22 pm

#245220

Hi
Have some questions on a project I'm working on:
I am using SQL Server Management Studio 2008
I am trying to add multiple rows of data to a single column. What i have is a datatable that has 4 columns:
ProjectID (Int. PK. NoNULL) , Project Name (text, nulls), MaterialsNeeded (text, nulls), Directions(text,nulls)
I want the MaterialsNeeded column to be able to be multplie rows for the same project in the datatable...Is it possible?
My end result would be for the user to be able to pull up a specific project by name and have everything for that project there. Including ALL the MaterialsNeeded, even if it were 108 Different items.
So it would look like this on the form: ( Dots won't be there...)
....Bird House...........................................2" x 8" x 6' White Ash
.............................................................Box of Nails
.............................................................Wood Glue
.............................................................6" x 1/4" Wooden Dowl Rod
.............................................................Paint
............................................................1.) Cut the 6' section of wood into 4 equal lengths.
............................................................2.) Glue two ends......etc.
Any help would be greatly appreciated... Now please keep in mind that I know VERY LITTLE about databases. I am teaching myself threw the web and different forums what I need to learn... I have some schooling, but due to financial reasons I am not able to go back yet. I do however want to and plan to do so, till then 'help'.... please.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Ok, I don't want to be too blunt but.... That's the kind of database 'design' that makes most of us run screaming.

Columns should hold atomic values, meaning values can't be broken down further such as a project name or a project commencement date, etc. Tables should hold data of one thing. From what you've said, you need a Project table and a Materials table at the very least.

Maybe take a read through all these first:

http://www.sqlservercentral.com/stairway/72400/

http://www.sqlservercentral.com/articles/Normalization/74241/

http://www.sqlservercentral.com/articles/Normalization/74381/

http://www.sqlservercentral.com/articles/Normalization/74437/

I would also strongly recommend Louis Davidson's SQL 2008 database design book (you can find it on Amazon)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

ajk825 (10/25/2011)
I want the MaterialsNeeded column to be able to be multplie rows for the same project in the datatable...Is it possible?

This is what we call a "one-to-many relationship" and yes, it is possible - provided you model your data the right way.

I strongly suggest to follow links provided by Gail - focus on Data Modeling, start with Entity-Relationship Modeling a.k.a. E/R then move to Data Normailization. My suggestion is to master 3NF.

Hope this helps.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Dyn-o-mite!! Valued Member Points: 67 More actions · Answer 3

In this case you need to break down the 'Project' table. As each project can have multiple 'Material Needed' values, try creating a new table ( say table_MaterialsNeeed ), shift 'Material Needed' column from your main table to the new table, and relate two tables with the projectID column. The end result would look like this.

Table 1: table_Project

ProjectID Project Name

--------- -------------

1 ABC

2 XYZ

3 MNO

Table 2: table_MaterialsNeeed

ProjectID Materials Needed

--------- ----------------

2 Sticks

2 Glue

1 Screws

3 Woods

2 Papers

You can infer by looking that project 'ABC' needs Sticks, Glues and Papers. The two tables are related by the key 'Project ID'.

An example of a Query to find out what materials needed for a particular project ( say project 'ABC' ) would be..

SELECT * FROM table_MaterialsNeeed WHERE ProjectID = 2

Hope this will assist you. Post a comment in case of further queries.

GSquared SSC Guru Points: 260824 More actions · Answer 4

You can do your original idea. It's a really bad idea, but it can be done. My first database worked that way, and it was a mess in very, very short order.

Much better to break the data down into a sub-table with one row per material you need, and a link (called a "foreign key") back to the projects table.

Better would be a projects table, a materials table, and a linking table between them. (Called a "many-to-many join".)

Details are as per Gail's post.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Here's a wee script to get the ball rolling. No PK's / FK's yet (or even indexes), and not even fully normalised, but simple enough to use as a framework to explain these principles.

DROP TABLE #Projects

CREATE TABLE #Projects (ProjectID INT IDENTITY (1,1), ProjectName VARCHAR(50), OtherAttributesOfProject VARCHAR(70))

INSERT INTO #Projects (ProjectName, OtherAttributesOfProject)

SELECT 'Big Bird House', 'Average completion time/average cost/key supplier etc etc' UNION ALL

SELECT 'Small Bird House', 'Other properties of the project'

--SELECT * FROM #Projects

DROP TABLE #Materials

CREATE TABLE #Materials (MaterialID INT IDENTITY (1,1), MaterialName VARCHAR(50), MaterialUnits VARCHAR(50))

INSERT INTO #Materials (MaterialName, MaterialUnits)

SELECT 'White Ash', 'Plank 2" x 8" x 6''' UNION ALL

SELECT 'Nails', 'Box of 40 x 1"' UNION ALL

SELECT 'Nails', 'Box of 80 x 1"' UNION ALL

SELECT 'Wood Glue', 'Tube 100ml' UNION ALL

SELECT 'Wooden Dowl Rod', '6" x 1/4"' UNION ALL

SELECT 'Paint', '250ml tin'

--SELECT * FROM #Materials

DROP TABLE #ProjectMaterials

CREATE TABLE #ProjectMaterials (ProjectMaterialID INT IDENTITY (1,1), ProjectID INT NOT NULL, MaterialID INT NOT NULL, Quantity DECIMAL (8,2))

INSERT INTO #ProjectMaterials (ProjectID, MaterialID, Quantity)

SELECT 1,1,2 UNION ALL

SELECT 1,3,1 UNION ALL

SELECT 1,4,2 UNION ALL

SELECT 1,5,2 UNION ALL

SELECT 1,6,1 UNION ALL

SELECT 2,1,1 UNION ALL

SELECT 2,2,1 UNION ALL

SELECT 2,4,1 UNION ALL

SELECT 2,5,1 UNION ALL

SELECT 2,6,1

--SELECT * FROM #Recipe

DROP TABLE #Methods

CREATE TABLE #Methods (MethodID INT IDENTITY (1,1), MethodText VARCHAR(MAX))

INSERT INTO #Methods (MethodText) SELECT 'Cut the 6'' section of wood into 4 equal lengths.' + CHAR(13) + 'Glue two ends......etc.'

--SELECT * FROM #Methods

DROP TABLE #ProjectMethods

CREATE TABLE #ProjectMethods (ProjectMethodID INT IDENTITY (1,1), ProjectID INT, MethodID INT)

INSERT INTO #ProjectMethods (ProjectID, MethodID) SELECT 1,1 UNION ALL SELECT 2,1

--SELECT * FROM #ProjectMethods

SELECT p.ProjectName, m.MaterialName, m.MaterialUnits, pm.Quantity

FROM #Projects p

LEFT JOIN #ProjectMaterials pm ON pm.ProjectID = p.ProjectID

LEFT JOIN #Materials m ON m.MaterialID = pm.MaterialID

WHERE p.ProjectName IN ('Big Bird House', 'Small Bird House')

ORDER BY p.ProjectName

SELECT p.ProjectName, m.MethodText

FROM #Projects p

INNER JOIN #ProjectMethods pm ON pm.ProjectID = p.ProjectID

INNER JOIN #Methods m ON m.MethodID = pm.MethodID

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 6

GSquared (10/27/2011)
You can do your original idea. It's a really bad idea, but it can be done.

Agreed. Politicians follow that very same principle in a daily basis 🙂

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

ACinAZ SSCommitted Points: 1849 More actions · Answer 7

ProjectDirections should also be a separate table. Something like this (that'll drive JC crazy...):

CREATE TABLE #ProjectDirections (

ProjectID INT IDENTITY (1,1)

, StepNumber INT

, StepDirection VARCHAR(1000)

)

Primary key would be ProjectID, StepNumber.