SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 schema question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Mister Ed
Starting Member

10 Posts

Posted - 08/02/2010 :  14:57:57  Show Profile  Reply with Quote
I've inherited an ancient Access database that I'm migrating to MS SQL. Have a question about how to improve a particular table.

The table contains records for lodging establishments in a U.S. state. Each row represents a particular establishment, and there are dozens and dozens of columns. Among these columns are over a dozen that name various activities available in the area where the establishment is located. Each activity has its own separate column, and the values are set to true or false. I sense there is a better way to handle this.

The table will be queried with a web form, and among the form components will be checkboxes for each of these activities.

What would be the best schema for the table? Should I create another table for activities (each activity being a row), and join it to the original table?

Or is there a smarter way to handle this?

X002548
Not Just a Number

15586 Posts

Posted - 08/02/2010 :  15:08:59  Show Profile  Reply with Quote
my first advice....

Take the table in Access, and create it, as is, with the data, in SQL Server

Then Post the DDL after you've done that

This will be your Access Stagin Area...in it's own separate db on SQL Server



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 08/02/2010 :  15:10:06  Show Profile  Visit tkizer's Homepage  Reply with Quote
Yes a new table should be created to handle the activities:

Perhaps like this:
ActivityID
ActivityName
LodgeID

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Mister Ed
Starting Member

10 Posts

Posted - 08/25/2010 :  10:36:59  Show Profile  Reply with Quote
thanks for the responses. Sorry for the delay--had to take care of some other demands before getting back to this.

Brett, you suggest getting the access data into SQL as a first step. It's looking like this will need to be in SQL Server Express. What's the best way to import the data?

I have SQL 2008 on my development machine, but the finished website will only have access to SQL 2005 Exp. Can I import the Access data into my local SQL 2008, configure everything, and then move that to SQL 05 Exp? Just looking for the smartest and most efficient means to get the data into its ultimate home.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 08/25/2010 :  13:34:13  Show Profile  Reply with Quote
Well, let's try this

Can you EXPORT the data from access into a delimited form?

If yes, then we can bcp the data in



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 08/25/2010 :  13:39:44  Show Profile  Visit tkizer's Homepage  Reply with Quote
Use SQL Server 2005 Express on your development machine and not SQL Server 2008. You could run into problems in 2005 if you are developing on 2008.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Mister Ed
Starting Member

10 Posts

Posted - 08/25/2010 :  14:25:26  Show Profile  Reply with Quote
Thanks--I installed the SQL Express 2005 toolkit on the web server and used the DTS Wizard...so got all the tables out of Access and into a new SQL 2005 db.

Trying to figure out the best way to export the schema for the table referenced in my original post so I can post it as Brett sugtgested.

Go to Top of Page

Mister Ed
Starting Member

10 Posts

Posted - 08/25/2010 :  14:37:15  Show Profile  Reply with Quote
Not sure if this is what Brett had in mind, but in SQL Server Management Studio Express, I selected the table, right-clicked and selected "Script Table as > CREATE to > New Query Editor Window", and then copied and pasted the results below:

USE [maineinns_staging]
GO
/****** Object:  Table [dbo].[MaineInnMembers]    Script Date: 08/25/2010 14:31:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MaineInnMembers](
	[ID] [int] NOT NULL,
	[PropertyName] [nvarchar](255) NULL,
	[Designation] [nvarchar](255) NULL,
	[Innkeeper] [nvarchar](255) NULL,
	[Address] [nvarchar](255) NULL,
	[City] [nvarchar](255) NULL,
	[State] [nvarchar](255) NULL,
	[Zip] [nvarchar](255) NULL,
	[Phone1] [nvarchar](255) NULL,
	[Phone2] [nvarchar](255) NULL,
	[Fax] [nvarchar](255) NULL,
	[Email] [nvarchar](255) NULL,
	[Web] [nvarchar](255) NULL,
	[Region] [nvarchar](255) NULL,
	[Rooms] [int] NULL,
	[Suites] [int] NULL,
	[Cottages] [int] NULL,
	[SingleRoom] [nvarchar](255) NULL,
	[DoubleRoom] [nvarchar](255) NULL,
	[Suite] [nvarchar](255) NULL,
	[Cottage] [nvarchar](255) NULL,
	[Alt_Locale1] [nvarchar](50) NULL,
	[Alt_Locale2] [nvarchar](50) NULL,
	[Description] [nvarchar](max) NULL,
	[Enhanced_Description] [nvarchar](max) NULL,
	[MeetingFacilities] [int] NULL,
	[CreditCards] [nvarchar](255) NULL,
	[Pets] [bit] NOT NULL,
	[Restaurant] [bit] NOT NULL,
	[WheelchairUnit] [bit] NOT NULL,
	[TDD] [bit] NOT NULL,
	[VisualSystem] [bit] NOT NULL,
	[Openperiod] [nvarchar](255) NULL,
	[IndoorPool] [bit] NOT NULL,
	[OutdoorPool] [bit] NOT NULL,
	[Childcare] [bit] NOT NULL,
	[AirConditioning] [bit] NOT NULL,
	[FitnessCenter] [bit] NOT NULL,
	[Movies] [bit] NOT NULL,
	[CableTV] [bit] NOT NULL,
	[Lounge] [bit] NOT NULL,
	[Dataports] [bit] NOT NULL,
	[Transportation] [bit] NOT NULL,
	[Whirlpool] [bit] NOT NULL,
	[HotTub] [bit] NOT NULL,
	[Kitchenettes] [bit] NOT NULL,
	[AgencyCommission] [bit] NOT NULL,
	[Fireplaces] [bit] NOT NULL,
	[Laundry] [bit] NOT NULL,
	[RoomService] [bit] NOT NULL,
	[agriculturalfairs] [bit] NOT NULL,
	[amusementparks] [bit] NOT NULL,
	[antiquing] [bit] NOT NULL,
	[bicycling] [bit] NOT NULL,
	[birding] [bit] NOT NULL,
	[boatcruises] [bit] NOT NULL,
	[canoeing] [bit] NOT NULL,
	[xcskiing] [bit] NOT NULL,
	[fishing] [bit] NOT NULL,
	[golfing] [bit] NOT NULL,
	[hiking] [bit] NOT NULL,
	[hunting] [bit] NOT NULL,
	[islands] [bit] NOT NULL,
	[kayaking] [bit] NOT NULL,
	[lighthouses] [bit] NOT NULL,
	[musmshistsites] [bit] NOT NULL,
	[music] [bit] NOT NULL,
	[scenicbyways] [bit] NOT NULL,
	[shopping] [bit] NOT NULL,
	[snowmobiling] [bit] NOT NULL,
	[statenatlpark] [bit] NOT NULL,
	[summeryouthcamp] [bit] NOT NULL,
	[whalewatching] [bit] NOT NULL,
	[whtwtrrafting] [bit] NOT NULL,
	[windjammer] [bit] NOT NULL,
	[airport] [bit] NOT NULL,
	[highspeed] [bit] NOT NULL,
	[wifi] [bit] NOT NULL,
	[spaonsite] [bit] NOT NULL,
	[spanearby] [bit] NOT NULL,
	[WorldResID] [nvarchar](50) NULL,
	[EnhancedListing] [bit] NOT NULL,
	[Image1] [nvarchar](50) NULL,
	[Image2] [nvarchar](50) NULL,
	[InnLinkID] [nvarchar](50) NULL,
	[InnLinkURL] [nvarchar](50) NULL,
	[beachnearby] [bit] NOT NULL,
	[lakenearby] [bit] NOT NULL,
	[marinanearby] [bit] NOT NULL,
	[oceannearby] [bit] NOT NULL,
	[tenniscourtsnearby] [bit] NOT NULL,
	[beachclose] [bit] NOT NULL,
	[lakeclose] [bit] NOT NULL,
	[marinaclose] [bit] NOT NULL,
	[oceanclose] [bit] NOT NULL,
	[tenniscourtsclose] [bit] NOT NULL
) ON [PRIMARY]


As you can see, this really doesn't seem like the most sensible way to handle the data. Any advice appreciated.
Go to Top of Page

Mister Ed
Starting Member

10 Posts

Posted - 09/07/2010 :  12:28:05  Show Profile  Reply with Quote
I don't suppose anyone has a second to look at this schema and point me in the right direction as far as improving things?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 09/07/2010 :  12:40:32  Show Profile  Visit tkizer's Homepage  Reply with Quote
I'd put all of those different attributes into another table, and then use a third table to join the main table and this attribute table together.

Here's a short example:

PersonTable
PersonId

AttributeTable
AttributeId
AttributeName

PersonAttributeTable
AttributeId
PersonId

By the way, I put Table on the end of the names just to make it clear that's a table. Do not do this for your actual table names.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 09/07/2010 :  12:41:43  Show Profile  Visit tkizer's Homepage  Reply with Quote
In my example, the Attribute table would contain each of your current bit columns and any other attributes like that.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Jake Shelton
Yak Posting Veteran

74 Posts

Posted - 09/07/2010 :  19:34:37  Show Profile  Reply with Quote
This one piqued my interest, how best should the OP get the data from the old table into the normalised ones?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 09/07/2010 :  19:41:59  Show Profile  Visit tkizer's Homepage  Reply with Quote
You'd manually load the attributes into the attribute table.

Then:

INSERT INTO Person
SELECT PersonName, ... --exclude ID column as you'd make that an identity column
FROM MainTable

We'd then run an INSERT for each attribute:

INSERT INTO PersonAttribute
SELECT 1, PersonId
FROM Person
WHERE Attribute1 = 1


INSERT INTO PersonAttribute
SELECT 2, PersonId
FROM Person
WHERE Attribute2 = 1

...

So you'd only load the "true" values in the PersonAttribute table. If a person has that attribute as "false", then the row does not get created.

Use an identity column for both the Person and Attribute tables so that it is auto-generated.

This design means you can add attributes without making a schema change as the attribute is just data and not a column. That's the huge benefit of this approach, plus it means it's in the correct normal form.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Mister Ed
Starting Member

10 Posts

Posted - 09/09/2010 :  16:40:34  Show Profile  Reply with Quote
Thanks very much for the specific advice. I'll see what I can do with this and get back to you.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 09/09/2010 :  16:45:22  Show Profile  Visit tkizer's Homepage  Reply with Quote
You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000