July 7, 2014 at 7:59 am
Hello, What I am asking is not difficult but I wanted some more input of easier ways creating a certain database. So I am making a database for cars for a friend at a carwash. What I have to do is I created a program using C# that the employee inputs the cars make model and color into the tablet and it shows on a big tv screen inside the store the employees washing the cars can see what car is it they have finished or not finished.
So I thought I would make a database for cars, than manually inputting all car make and models in C#.
I was thinking of making a local server inside C#.
The problem I am having is I don't want to keep inputting all the car makes and models, since it will take forever. Is there a easier way using foreign key constraint and can I PLEASE have an example? thank you this is what I got
CREATE TABLE tASTON
(AsM_ID int Primary Key Not Null IDENTITY(1,1),Make Nvarchar(50), Model nvarchar (50))
Insert into tASTON(AsM_ID,Make,Model) Values ('Aston Martin','DB4')
Insert into tASTON (AsM_ID,Make,Model) Values ('Aston Martin','DB5')
Insert into tASTON (AsM_ID,Make,Model) Values ('Aston Martin','DB6')
Insert into tASTON (AsM_ID,Make,Model) Values ('Aston Martin','DB7')
Insert into tASTON (AsM_ID,Make,Model) Values ('Aston Martin','Vanquish') etc...
CREATE TABLE tHonda
(Hond_ID int Primary Key Not Null IDENTITY(1,1),Make Nvarchar(50), Model nvarchar (50))
Insert into tHonda(Hond_ID,Make,Model) Values ('Honda','Civic')
Insert into tHonda(Hond_ID, Make,Model) Values ('Honda','Accord')
etc..
Create Table tColor
(C_ID int Primary Key Not Null, Color nvarchar (15))
Insert into tColor
(C_ID,Color) Values (1,'White')
Insert into tColor
(C_ID,Color) Values (2,'Black')
Insert into tColor
(C_ID,Color) Values (3,'Blue')
Insert into tColor
(C_ID,Color) Values (4,'Green')
Insert into tColor
(C_ID,Color) Values (5,'Gold')
Insert into tColor
(C_ID,Color) Values (6,'Silver')
Insert into tColor
(C_ID,Color) Values (7,'Gray')
Insert into tColor
(C_ID,Color) Values (8,'Red')
Insert into tColor
(C_ID,Color) Values (9,'Maroon')
Insert into tColor
(C_ID,Color) Values (10,'Purple')
Insert into tColor
(C_ID,Color) Values (11,'Orange')
July 7, 2014 at 8:28 am
I'd create a table for the Make. Then I'd create a table for MakeModel. That will have the primary key from the Make and the Model. Then you create a relationship using a foreign key constraint. Here's how to do that in SQL Server. Then you only enter the make once. You'll only enter the model once. Then you can use the primary key from that table in the other table. It's all about relational database design. Building out the structures is very similar to building objects in code (but different, since objects have no problem with repeating values, which you want to avoid in relational storage).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 7, 2014 at 9:00 am
I would also refrain from prefixing your tables with "t". And don't be scared to give your tables and columns longer names so it is easy to understand what they are.
_______________________________________________________________
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 7, 2014 at 9:15 am
So I went and updated my query a bit. Am I on the right track?
CREATE TABLE tCAR
(
CAR_ID int Primary Key Not Null IDENTITY(1,1)
, BrandID int FOREIGN KEY REFERENCES tBrand(BrandId)
, Make Nvarchar(50)
, Model nvarchar (50)
)
Insert Into tcar
(CAR_ID, Make) Values(1,'ASTON MARTIN')
Insert Into tcar
(CAR_ID, Make) Values(2,'BENTLEY')
Insert Into tcar
(CAR_ID, Make) Values(3,'CADILLAC')
Insert Into tcar
(CAR_ID, Make) Values(4,'CHEVROLET')
Insert Into tcar
(CAR_ID, Make) Values(5,'DODGE')
etc...
Create Table tBrand
(ID int Not Null, BrandID int Not Null, Model Nvarchar (50))
INSERT INTO tBrand VALUES
(1, 1, 'CL_MODELS', 'CL Models (4)'),
(2, 1, '2.2CL', ' - 2.2CL'),
(3, 1, '2.3CL', ' - 2.3CL'),
(4, 1, '3.0CL', ' - 3.0CL'),
(5, 1, '3.2CL', ' - 3.2CL'),
(6, 1, 'ILX', 'ILX'),
(7, 1, 'INTEG', 'Integra'),
(8, 1, 'LEGEND', 'Legend'),
(9, 1, 'MDX', 'MDX'),
(10, 1, 'NSX', 'NSX'),
(11, 1, 'RDX', 'RDX'),
(12, 1, 'RL_MODELS', 'RL Models (2)'),
(13, 1, '3.5RL', ' - 3.5 RL'),
(14, 1, 'RL', ' - RL'),
(15, 1, 'RSX', 'RSX'),
(16, 1, 'SLX', 'SLX'),
(17, 1, 'TL_MODELS', 'TL Models (3)'),
(18, 1, '2.5TL', ' - 2.5TL'),
(19, 1, '3.2TL', ' - 3.2TL'),
(20, 1, 'TL', ' - TL'),
(21, 1, 'TSX', 'TSX'),
(22, 1, 'VIGOR', 'Vigor'),
(23, 1, 'ZDX', 'ZDX'),
(24, 1, 'ACUOTH', 'Other Acura Models'),
(25, 2, 'ALFA164', '164'),
(26, 2, 'ALFA8C', '8C Competizione'),
(27, 2, 'ALFAGT', 'GTV-6'),
(28, 2, 'MIL', 'Milano'),
(29, 2, 'SPID', 'Spider'),
(30, 2, 'ALFAOTH', 'Other Alfa Romeo Models'),
(31, 3, 'AMCALLIAN', 'Alliance'),
(32, 3, 'CON', 'Concord'),
(33, 3, 'EAGLE', 'Eagle'),
(34, 3, 'AMCENC', 'Encore'),
(35, 3, 'AMCSPIRIT', 'Spirit'),
(36, 3, 'AMCOTH', 'Other AMC Models'),
(37, 4, 'DB7', 'DB7'),
(38, 4, 'DB9', 'DB9'),
(39, 4, 'DBS', 'DBS'),
July 7, 2014 at 9:34 am
You are on the right path but you need to normalize Make and Model. Consider what you would have to do with this structure if the Model name was wrong. You would have to update the whole table.
There are a lot of ways this could be done. I chose to have the Model have a reference to the Make. The reality is that there are not a lot of cars named the same thing across manufacturers. The car companies actually put a lot of effort into NOT using the same name for a car as somebody else.
Here is what I came up with.
create table Make
(
MakeID int identity primary key clustered,
MakeName varchar(50) not null
)
create table Model
(
ModelID int identity primary key clustered,
ModelName varchar(50) not null,
MakeID int not null FOREIGN KEY REFERENCES Make(MakeID)
)
create table Colors
(
ColorID int identity primary key clustered,
ColorName varchar(50) not null
)
create table Cars
(
CarID int identity primary key clustered,
ModelID int not null,
ColorID int not null FOREIGN KEY REFERENCES Colors(ColorID),
CarYear int not null --this could easily be another table too but not required.
)
insert into Make(MakeName)
select 'Honda' union all
select 'Astin Martin'
insert into Model(ModelName, MakeID)
select 'Accord', 1 union all
select 'Civic', 1 union all
select 'DB4', 2 union all
select 'DB5', 2
insert Colors(ColorName)
select 'Red' union all
select 'Blue' union all
select 'Green'
insert Cars(ModelID, ColorID, CarYear)
select 1, 1, 2005 union all
select 3, 3, 2014
select c.CarYear
, co.ColorName
, ma.MakeName
, m.ModelName
from Cars c
inner join Colors co on co.ColorID = c.ColorID
inner join Model m on m.ModelID = c.ModelID
inner join Make ma on ma.MakeID = m.ModelID
_______________________________________________________________
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 7, 2014 at 9:46 am
Oh thank you very much! I now see exactly what your talking about and of what your query is stating. The problem I have is I know functions of the select statements, where clauses, inner joins etc. But When ever I am instructed to make a database with tables I don't know where to start. I mean the basics yes but I just seem to kind of blank out. As where when the tables are already there I know how to create stored procedures and do pretty good queries working with tables. I don't know maybe its just me. I have to keep creating different type of databases for different requirements I guess to get used to it. But thanks a lot!
July 7, 2014 at 9:50 am
swaseem345 (7/7/2014)
Oh thank you very much! I now see exactly what your talking about and of what your query is stating. The problem I have is I know functions of the select statements, where clauses, inner joins etc. But When ever I am instructed to make a database with tables I don't know where to start. I mean the basics yes but I just seem to kind of blank out. As where when the tables are already there I know how to create stored procedures and do pretty good queries working with tables. I don't know maybe its just me. I have to keep creating different type of databases for different requirements I guess to get used to it. But thanks a lot!
Designing tables takes practice and a willingness to see what you can improve on the next time. Depending on you exact requirements I might make some changes to what I posted but it should at least demonstrate the basic concepts well enough.
Glad I was able to help.
_______________________________________________________________
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 7, 2014 at 9:56 am
If you're really going to be building a lot of databases, I'd suggest reading Louis Davidson's book. It's a great way to learn how to do database design.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply