Solved

Creating a TABLE with many columns

Posted on 2008-10-09
3
221 Views
Last Modified: 2010-03-20
I am new to SQL and I need a table to store data on the top 50 wines sold in a region. The key would be the regionID and then I need to store the 50 wineIds of the most sold wine for that region. Do I need to create 50 different columns or is there a way to create one column with 50 different values likwe an array?
0
Comment
Question by:acsakany
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 22

Expert Comment

by:dportas
ID: 22681422
Here's how I'd do it:

CREATE TABLE RegionWineRankings
(RegionID INT NOT NULL REFERENCES Regions (RegionId),
 WineID INT NOT NULL REFERENCES Wines (WineId),
 Ranking SMALLINT NOT NULL CHECK (Ranking BETWEEN 1 AND 50),
 PRIMARY KEY (RegionID, WineID)
);

I am not assuming that (RegionID,Ranking) is unique because I don't know if you want to allow tied rankings.
0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 50 total points
ID: 22681431
>>Do I need to create 50 different columns

NO!

Create Table Wine(
  WineName nvarchar(50)
/* any other info about the wine */
)

Create Table Region(
  RegionName nvarchar(100)
 /* any other info on the region */
)

Create Table WineRegion(
 WineName nvarchar(50),
 RegionName nvarchar(100),
 DateSoldAtTop datetime
/* any other info that releate the wine to the region */
)
0
 
LVL 57
ID: 22681440

  When one talks about using SQL, which is Structured Query Language, you are talking about using a relational database.  

  When designing a relational database, you follow some basic rules.  The process of following those rules is called normalization.  If you don't follow the rules, then the database doesn't work to well and neither does SQL queries.

  For example, in a single field, you don't store multiple values "1 hammer, 20 10D nails"  .  What you got is four values; two qty's and 2 descriptions of something.  You also don't repeat fields:

  Phone 1, phone 2, phone 3, phone 4.

  The reason is if you wanted to know how many people had at least 3 phone numbers, you'd need to look at each record and each of the phone fields.  SQL is designed to work with *sets* of records.   Each of those phone fields should be a record in a seperate table.

  So back to your question.  You should have the following:

tblRegion - One record per region
RegionID - Autonumber - A unique number to indentify the region - This is the Pirmary Key
Description - Text - Description of the region

tblWines - One record per wine
WineID - Autonumber - Again the PK
RegionID - Forgein key - This means that this value is a primary key in another table
SalesDollars - Currency - Sales of this wine.

  Now it's easy to look at tblWines group by region, sort descending by SalesDollars, and pick only the top 50 in each group.  Then with that list, use the RegionID to get a record from tblregion and the description for use in a form or report.

HTH,
JimD.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Recursion 6 43
SP result not being displayed 5 65
Need quicker response from an Execption table 11 41
Where clause to fliter varchar with Characters 12 52
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…
Suggested Courses

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question