I wonder instead of just brainstorming, there probably is
a very standard and a simple way to do database schema design.
let's say we are doing a website. the user can go over
and type in the movie name and zipcode, and the website
will return all the theaters showing that movie and at what
time, for theaters in THAT zipcode only (for simplicity).
so how do we just start and use a standard method that
can be simple and very accurate to ensure good tables design?
I have fairly simple table queries, and only a few dicey things, which I can handle in access. Is their a design tool for sql as easy and convenient as access?
Can someone suggest a good beginner's book on sql server 2005?
I am new to using SQL Server but will be having to get to know it at work and have downloaded SQL Server 2005 Express to tinker with at home.
What are the easiest methods to set up a relational database?
Are there any tools you can download to give SQL Server or the express version functionality similar to Access?
Can you get a tool to allow you to not only design tables in design view but then go on to create relationships and a database model?
Please excuse my ignorance if this is possible already, can anyone provide any links to getting started links?
There seems to be plenty out there about writing queries and stored procedures etc. but I haven't found as much about setting up a database from scratch.
Let me explain what I'm trying to do and see if I can get any suggestions. I have some tables in a database to track "Required Reading" so that users who login must read documents by a certain date.
My first idea was to have a table called Reading that would store each UserID and every DocID from the Documents table. Then when the User logged in I would do a SELECT * FROM Reading WHERE UserID = 'User1' to filter down to all of the documents for the current user. So if I have 10 users and 10 documents then I would have 100 records in the Reading table.
The only problem with that is that every user logged in would have the Reading table open at the same time but no two users should ever be editing the same record. The user would have an exclusive recordset based on his UserID. I just don't know if this would cause a conflict.
The second idea is to have the same Documents table but when a user logs in, create them a unique table. So if User1 logs in I would check to see if a User1 table exists and if not create it. The same for User2 etc.
User1 ------------------ DocID nvarchar(16) (PrimaryKey) Read bit ReadDate smalldatetime
User2 ------------------ DocID nvarchar(16) (PrimaryKey) Read bit ReadDate smalldatetime
Can you tell me how to represent the problem below?
I want to be able to store different types of information for products in different categories.
So Category A maybe TV's and category B maybe Sofa's and Cateogry C maybe Car. There will however be some common data.
So for a TV I may want to store screen size etc.. and for a Sofa if its leather and for cars maybe whethers its a Saloon, 4x4 etc.. The common data maybe Manufacturer, Price etc..Obviousely each peice of info will be of a different data type.
How best do I represent this in a database because I will have lots of categories of products and the products will be used for filtering too. So I need to create a solution which is not too costly on performance.
To give you an example of what I'm intending on doing..lets look at desktop computers which will have the following product specific info to filter on: http://computing.kelkoo.co.uk/ctl/do/compareProducts?back=%2Fc-111801-desktop-computers.html&catId=111801&pid1=18445816&pid2=12461415 and for TV's we have http://audiovisual.kelkoo.co.uk/ctl/do/compareProducts?back=%2Fc-100311823-lcd-tvs.html&catId=100311823&pid1=18052959&pid2=18704336
or http://shopcompare.eu/ash/search.php?phrase=GPS and http://shopcompare.eu/ash/search.php?phrase=palmtops&cid=28
Thanks in advance, any guidance even to any online tutorial would be appreciated.
QUESTIONs:What schema is the best for high speed search for a classified web application? Is our schema design looks OK? It is a STAR schema and will be used for OLTP type app. Is this OK? or Are we missing something? Please let me know if you need more diagramatic description. BACKGROUND:Our group is making a classified website (like classified.yahoo.com) where people can place online ad to sell items. like cars, computers, electronics etc. Users will fill out webform for each category(car or computers) with all attributes of the item forsell to post an add.
Main 3 operations the web users will perform: Quick Searching(most frequent): category=car, subcategory=sedan, country=USA city=LosAngeles Zip=empty Advanced Search(less frequent): User can include all fieldsattributes or then can user a subset to query to do advanced search like : category=car, subcategory=sedan, country=USA, city=Los Angeles, Year= in(00, 03), transmission=Auto, engine=V6, Maker=Honda, Model=Accord, color=Red and Price < 10000 category=car, subcategory=SUV, country=USA, city=NY, Year= in(99, 00, 03), transmission=Auto, Maker=Toyota, and Price between 11000 and 14500
Insert(least frequent): By filling out web form. For Car, the form will have different dimensionsattributes (year, make, model, transmission, mileage, color, price etc)
Current Schema design: Set of core dimensionlookup tables: stores corecommon attributes for lookups eg. status={open, new, expired}, country={USA, Canada, India,...} One custom Lookup table: for all custom attribute lookup: transmission{auto, manual}, engine type{V4, V6} for car, processor{PII, PIII, PIV), RAM(512MB, 1GB, 2GB} for Computer & so
3 Fact Tables: Main factPivot table that stores all sell common attributes eg. price, title, year_made, post_date, expire_date, user_name, description etc. One fact table to store custom string, int, float, date field values of ads. One fact table to store custom dropdown field selection values
Concerns and issues: Looking at the schema, it seems to be a STAR schema with multiple fact tables where all core lookup tables connected to the main Pivot table and custom lookup table connected to the 2nd and 3rd fact table.
Quick search only queries the Pivot fact table. While Advanced search query requires to join 3 fact tables. Both query requires to join 3 fact tables with all dimension tables(15 to 20 each having avg of 20 values) to get the look up names so that users sees text instead of ids. Search speed is the Main concerns. Insertionupdate speed doesn't matter that much as that is less frequently done.
Here is the current schema. product_category - product_category_id - other fields, ... product - product_id - product_category_id - ....other fields
Now, for each product type, it has a distinct set of specifications. For example: Books type, will have author, publisher, pages, etc. Apparels type will have color, size, materials, etc. I don't think I can put all those specifications into one table - the product table above. How to design the schema to store product specifications? Should I create a separate spec table for each product type, such as book_product_spec, apparel_product_spec, electronics_product_spec, etc.? Please advise. Thanks.
Hello, we have some tables and views that are not assigned to the standard schema of dbo.Whenever I try to use a design time control and/or SQLDataSource connection control, it does not like objects other than those using the dbo. schema.Any ideas on how to resolve this issue?For example, her are a couple of views:dbo.vwCustomersdbo.vwCustomerDetailsord.vwOrdersord.vwOrderDetailsthe views with dbo.* I have no problem with.the views with ord. it tells me the view(s) do not exist.Thanks in advance!
in simple words it's about versioning at record level.ExampleTableEmployee - EmployeeId, EmployeeName,EmployeeAddress, DepartmentId,TableDesignationMap - EmployeeId, DesignationId, EffectiveDate,validityTableDepartment - DepartmentId, DepartmentTableDesignation - DesignationId, designationVia Modify-Employee-Details screen following are editableEmoyeeNameEmployeeAddressDepartmentDesignationthis screen should allow user to navigate through changes history.Example :Version -1EmoyeeName John SmithEmployeeAddress 60 NewYorkDepartment AccountsDesignation AccountantVersion -2EmoyeeName John SmithEmployeeAddress 60 NewYorkDepartment AccountsDesignation Chief Accountant - changedVersion -3EmoyeeName John SmithEmployeeAddress 60 NewYorkDepartment Sales - changedDesignation Marketing Manager - changedQuestion :What is the best proposed database design for maintaining historyrecords bound with version and retrieval techniqueBest RegardsSasanka
I'm using SQL Server 2008 R2 Management Studio. I have a view that I'd like to index. Obviously, that requires that the view be schema bound to the underlying table. I have found myriad explanations for how to programmatically create a schema bound view, but I've never created a view like that before, and since I'm more a mechanical engineer than a database manager, I'd like to be able to do it the 'easy' way, by just creating the view in Management Studio by going to the database, right clicking on 'Views', clicking on 'New View...' and then, hopefully, checking some box somewhere that schema binds the table to the view.
Hi There,This is related to a ms access database but since I use the SqlDataSource control I thought I should post here.I have a project that I was working on with this ms access db and using sql controls, everything was working just finesince one day I started getting "Object reference not set to an instance of an object" messages when I try to designa query or retrieve a schema, nothing works at design time anymore but at runtime everything is perfect, its a lotof work for me now to create columns,schemas and everything manually, I've tried reinstalling visualstudio, ado componentsbut nothing seems to fix it, did this ever happen to any of you guys?any tip is really appreciated thanks a lot
I am cutting my teeth on star schema design. I have a simple star schema I am building for Headounct analysis at work. I have a factless fact table where a row represents a head in the company. Each head is toed to a particulat week in a Date dimension tabel. There are additional dimensions for things like gender, ethnicity, marital status, age, etc. Now in my department dimension - it's hierarchical. In the DimDepartmnet there is a department which belongs to a company. Comapnies belong to divisions. Now the fun part. Each division has a headcount target for each year. Up to this point I am in a perfect star schema (no snow flaking). How would I integrate in this concept of a headcount target for each division for a given year?
We are using cognos on top of this star schema to provide reporting and analysis services if that is relevant. From the Star Schema design stand point... any thoughts?
Locally I develop in SQL server 2005 enterprise. Recently I recreated my db on the server of my hosting company (in sql server 2005 express).I basically recreated the tables and copied the data in it.I now receive the following error when I hit the DB:The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version.I heard something about running aspnet_regsql.exe, but I dont have that access to the DB. Also I dont know if this command does anything more than creating the membership tables and filling it with some default data...Any other solutions/thought on what this can be?Thanks!
Hello everybody!I'm using ASP.NET 3.5, MSSQL 2005I bought virtual web hosting .On new user registrations i have an error =(The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version. On my virtual machine it work fine but on web hosting i have an error =(What can you propose to me?
I would like to use SSIS tool to move the data from one database schema to another database schema.
For example:
Source table has
1. UserName (varchar 20) (no null)
2. Email (varchar 50) (can be null)
Destination table has
1. UserID (uniqueidentifier - GUID)
2. UserName (varchar 50) (no null)
3. EmailAddress (nvarchar 50) (can be null)
4. DateTime
Questions:
1. What controls do I use in my Data Flow to make data move between databases with different data types and include new value in UserID as a new GUID and DateTime as a date (GETDATE)?
OLE DB Source, OLE DB Destination, Data Converson and .....
How do I insert Guid and Date at the same time?
2. I have many tables to do data moving. Any sugestions? How do I architect my project? If I create many data flows for each table - it will look complicated.
How do SQL 2000 service packs play a role in upgrading? That is, can SQL 2000 Standard with no Service Packs(SP) be upgraded to SQL 2005 Standard, or does SQL 2000 Standard have to have a certain service pack??
I used SSEUtil to add a schema to my database but I am having problems. Used these steps:SSEUtil -c> USE "c:Rich.mdf"> GO>!RUN Resume.SQL//indicates success>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema not shown in list> USE master>GO>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema is shown in the queryIt appears that the schema is not added to the desired database, so when I try to use the schema in Visual Studio, the schema does not appear when I connect to the Rich.mdf database. Any ideas on what I am doing wrong or why this might be happening?ThanksKevin
Ok, I'm doing a football database for fixtures and stuff. The problem I am having is that in a fixture, there is both a home, and an away team. The tables as a result are something like this:
It's not exactly like that, but you get the point. The question is, can I do a fixture query which results in one record per fixture, showing both teams details. The first in a hometeam field and the second in an away team field.
Fixture contains the details about the fixture like date and fixture id and has it been played
Team contains team info like team id, name, associated graphic
TeamFixture is the table which links the fixture to it's home and away team.
TeamFixture exists to prevent a many to many type relationship.
Make sense? Sorry if this turns out to be really easy, just can't get my head around it at the mo!
I would like to create a table called product. My objective is to get list of packages available for each product in data grid view column while selecting each product. Each product may have different packages type (eg:- Nos, CTN, OTR etc). Some product may have two packages and some for 3 packages etc. Quantity in each packages also may be differ ( for eg:- for some CTN may contain 12 nos or in other case 8 nos etc). Prices for each packages also will be different that also need to show. Â How to design the table..Â
Product name  :  Nestle milk | Rainbow milk packages  : CTN,OTR, NOs |
CTN, NOs Price: 50,20,5 | 40,6
(Remarks for your reference):CTN=10nos, OTR=4 nos  | CTN=8 Nos
First of all I am planning to install sql 2005 standard and sql standard sp2 on vista ultimate professional using vs 2008 pro I have some questions about installing sql 2005 what is the best way to install sql 2005 on vista My article I got this from ms 403393 (upgrading sql standard 2005 to sql standard 2005sp2) 1 sql standard .(shortcut right click from cd/dvd not possible) I have heard command prompt start menu, click cmd type run D: cd/dvd exe From a wizard (sql sp2)
2. sql standard sp2 a. upgrade using a wizard b. open a command prompt (not sure if right click is best option) install dvd media example dqlserver2005sp2-kb921896-exe SKUUPGRADE=1(is run as administrator option on media) do I type run or what do I type ? c. go to system configuration page(where is this) you should see a single warnining you need to install sp2, the edition should say success if it does not please reapply d. on the registration page please enter your registeration number e. on the comments to install please enter database services, reporting services workstation components, books online and development tools( I wish to install all components)
f. on the instance page click default instance(default install) g. finish rest of wizard h. apply sql sp2
I also have of seperate question of upgrading sql from vista using a Configuring local admin account account (configuring administration from a wizard-will this notify me by default) I do not want my administrative priviledges elevated. I will be using default configurations for my installations......... I often wish there would a nice picture examples of doing this Please help
I'm banging my head against the wall. It's probably pretty simple so here goes...
I have a form that supposed to add records to a table on the database. I'm pretty sure I have the dataAdapter and Dataset set right but when I add a record, it complains that the primary key can’t be null. I’ve tried different things, including removing those fields and variables from the stored Proc, changing the Identity Properties of the Table, changing the AutoIncrement settings on the Dataset for that field, but all give me different errors. Nothing actually makes it work. This database has many records in it and the Primary key is a decimal field that starts at 5, then next was 10, then 28, then it started getting consecutive after 110. (111, 112, 113, etc.) (I'm porting over an old database to a new web site, so I'm somewhat contrained by the database.)
I guess my question is for adding records in to a database that has a primary unique key constraint. Certainly this is a pretty common thing. Obviously I do not want users to enter that in themselves, I thought it should simply autocreate/autonumber. So what is the best procedure and gotcha’s I need to watch out for?
if i have the following code, how do i know if the transaction was actually successful, so basically where in this code can i write TRANSACTION SUCCESFUL?
try { //Open up the connection conn.Open(); //Setup the Transaction trans = conn.BeginTransaction();
//First query in transaction pre_query = "Delete from Menu where spec_name=" + "'" + spec_name_array[i].Text.ToString() + "'" ;
//Second query in transaction query = "INSERT into Menu VALUES ('" + Calendar1.SelectedDate +"','" + spec_name_array[i].Text.ToString() +"','" + spec_desc_array[i].Text.ToString() +"','" +spec_price_array[i].Text.ToString() +"',1)"; SqlCommand comm = new SqlCommand(pre_query,conn); //Setup the command to handle transaction comm.Transaction = trans; //Execute first query comm.ExecuteNonQuery(); //Add in second query comm.CommandText = query; //Execute second query comm.ExecuteNonQuery(); trans.Commit(); } catch(SqlException ex) { com_label.Text = "Submission Not complete, did you forget the date or something else?"; //Undo all queries trans.Rollback(); } finally { conn.Close(); }
This may not be the right place for this post...but my head is hurting and I can't think right now.
I have a SQL table of users. Each record has a required 'Birthdate' field. I'd like to order this by the days left till the give birthday, keeping in mind that the year may be 1980 or their birthday might have been 2 weeks ago.
I have written 2 custom connection mgr€™s. One connects the data from an oracle source One connects so I can put the data on a sql server. These both work well and it makes it a lot easier across development, test, production.
But now I would like a good way to do this
I have 10 tables to copy from oracle. The only difference is just the name of the table.
If I have a 64 bit version of SQL 2005 installed, will "select @@version" indicate it is a 64 bit version? If not, how do I determine such? Thanks in advance.
