July 9, 2013 at 1:44 am
I am developing a very big project with a huge data to be store in database.I have more than 10 categories.I want to get data from online websites on hourly basis and save them
in my database.
For simplicity lets discuss only one category "Properties" with few columns
Here is table
CREATE TABLE [dbo].[properties](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[title] [nchar](10) NOT NULL,
[description] [ntext] NULL,
[property_type] [int] NOT NULL,
[beds] [int] NOT NULL,
[price][bigint] NOT NULL,
[website_id] [int] NOT NULL,
CONSTRAINT [PK_properties] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
INSERT INTO [test].[dbo].[properties]
([title]
,[description]
,[property_type]
,[beds]
,[price]
,[website_id])
SELECT 'title1','description of my property',2,5,50000000,2 UNION ALL
SELECT 'title2','description of my property',1,1,10000000,1 UNION ALL
SELECT 'title3','description of my property',1,3,10000000,3
SET IDENTITY_INSERT properties OFF
SELECT * from properties
In above data we can see there is website_id(ids of websites).Its values are 1=website1,2=website2 and 3=website3
Now if i search beds=1 and price=10000000,it will show me last two records(mean website 1 and website3 has your result)
Is above approach is good OR Should i created different tables for each website and then make search?
For example i do like three tables for property_website1,property_website2 and property_website3?
July 9, 2013 at 7:45 am
I think using 1 table should be fine if all the data is reasonably close to the same columns.
However you should not use ntext. The text and ntext datatypes are deprecated. It also a real pain to work with. Instead you should use nvarchar(max). I would question if you really need that much space though for a description. I would think that nvarchar(2000) should be plenty. Also you might want to consider your nchar(10) and change it to nvarchar(10).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 10, 2013 at 12:26 am
Sean Lange (7/9/2013)
I think using 1 table should be fine if all the data is reasonably close to the same columns.
Ok , Thanks
And I am crawling for new information on websites and save it in our database.What i am doing is,I am deleting all data from table and re-enter again the information to get updated.
Is there anyother way to do so?
Thanks in advance
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply