May 30, 2009 at 5:24 pm
Hello,
is this piece of code OK or should I add something to it? Each customer can contain a city, each city can containt a country.
create table Country(
[ID] int identity(1,1) not null primary key,
[Country] varchar(50) not null,
constraint PK_Country unique ([ID], [Country])
)
create table City(
[ID] int identity(1,1) not null primary key,
[City] varchar(50) not null,
[CountryID] int references Country ([ID]) on delete set null,
constraint PK_City unique ([ID], [City])
)
create table Customer(
[ID] int identity(1,1) not null primary key,
[Name] varchar(30) not null,
[Surname] varchar(30) null,
[Company] varchar(30) null,
[Address] varchar(100) null,
[TelephoneNumber] varchar(30),
[CityID] int references City ([ID]) on delete set null,
constraint PK_Customer unique ([ID], [Name])
)
Thanks!:hehe:
May 30, 2009 at 7:16 pm
Hello,
A couple of questions: Do you have a particular reason to include the ID columns in the Unique Constraints? I ask, as the ID columns are Identity columns, so will be unique by themselves. I presume you want the Country, City and Name to contain unique values?
Also, I personally prefer to prefix the ID columns with the Entity Name e.g. CityID. I find this helps to make the (application) code more readable.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
May 30, 2009 at 10:07 pm
John Marsh (5/30/2009)
the ID columns are Identity columns, so will be unique by themselves
Identity columns are not necessarily unique. If you never reseed the identity sequence, and never use IDENTITY_INSERT to insert duplicate values, they will be unique, but nothing about the IDENTITY attribute enforces uniqueness. It is the PRIMARY KEY constraint that will enforce uniqueness on those columns. Other than that quibble, I agree with your observations.
Since the [ID] fields will be unique by themselves, the UNIQUE constraints on ([ID], xxx) add nothing to the design but overhead. You could execute "INSERT INTO Country (Country) VALUES ('Elbonia')" millions of times with no errors.
If the intention was that the Country, City, and Name should be unique, take the [ID] fields out of the UNIQUE constraints. Also, using the "PK_" prefix in UNIQUE constraint names is misleading.
May 31, 2009 at 2:07 am
Thanks for your reply, I changed my code, hope it is OK now:
create table [Country](
[CountryID] int identity(1,1) not null,
[Country] varchar(50) not null,
constraint PK_Country primary key clustered ([CountryID]),
constraint UQ_Country unique nonclustered ([Country])
)
create table [City](
[CityID] int identity(1,1) not null,
[City] varchar(50) not null,
[CountryID] int references [Country] ([CountryID]) on delete set null,
constraint PK_City primary key clustered ([CityID]),
constraint UQ_City unique ([City])
)
create table [Customer](
[CustomerID] int identity(1,1) not null,
[Name] varchar(30) not null,
[Surname] varchar(30) null,
[Company] varchar(30) null,
[Address] varchar(100) null,
[TelephoneNumber] varchar(30),
[CityID] int references [City] ([CityID]) on delete set null,
constraint PK_Customer primary key clustered ([CustomerID]),
constraint UQ_Customer unique ([Name])
)
Next question 🙂
This are my tables: Classification, Service, Material, BillOfMaterials. Each material should have defined it's classification. In each service there can be more materials - that's why I use 'connecting table' BillOfMaterials. Is this principle OK? What indexes should I create on table BillOfMaterials?
create table [Classification](
[ClassificationID] int identity(1,1) not null,
[Description] varchar(30) not null,
constraint PK_Classification primary key clustered ([ClassificationID]),
constraint UQ_Classification unique ([Description])
)
create table [Service](
[ServiceID] int identity(1,1) not null,
[Description] varchar(30) not null,
[Price] money not null,
constraint PK_Service primary key clustered ([ServiceID]),
constraint UQ_Service unique ([Description])
)
create table [Material](
[MaterialID] int identity(1,1) not null,
[Name] varchar(50) not null,
[PurchasePrice] money not null,
[UM] varchar(3) not null,
[ClassificationID] int references [Classification] ([ClassificationID]) on delete set null,
constraint PK_MaterialID primary key clustered ([MaterialID]),
constraint UQ_Material unique ([Name])
)
create table [BillOfMaterials](
[ServiceID] int references [Service] ([ServiceID]),
[MaterialID] int references [Material] ([MaterialID]),
[Quantity] decimal(12,2) not null default 1.0,
constraint PK_BillOfMaterials unique ([ServiceID], [MaterialID])
)
May 31, 2009 at 2:56 am
_simon_ (5/31/2009)
Thanks for your reply, I changed my code, hope it is OK now:
You sure that a customer's name will be unique?
What indexes should I create on table BillOfMaterials?
Very hard to tell without seeing the queries that will run against that table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 31, 2009 at 3:04 am
Personally, I prefer prefixing the table name to all columns except foreign keys. It makes easier to understand and write code when joins are involved. For e.g.
SELECT Cust.ID, Cust.Name, Cit.ID, Cit.Name FROM dbo.Customers Cust INNER JOIN dbo.Cities Cit on Cust.CityID = Cit.ID
Now, see the difference if one had prefixed the table name in column names.
SELECT Cust.CustomerID, Cust.CustomerName, Cit.CityID, Cit.CityName FROM dbo.Customers Cust INNER JOIN dbo.Cities Cit on Cust.CityID = Cit.CityID
And also, are you sure that the unique constraint in customers table is appropriate? I mean there can be many people with the same name with a city.
You need to also verify the datatypes defined identity (key) columns. Does the no. of countries or cities ever going to be 2+ billions? Does 30 character wide column is enough to hold the customer name, surname, address, company etc.?
--Ramesh
May 31, 2009 at 3:26 am
OK, I removed unique constraint on Customer table.
Ramesh, you're right, I will change it back, it is more readable that's way 🙂
This is my own Project for learnign purposes (IS for brothel), so I'm pretty sure number of cities would not expand 2 billion... Now I am building C# classes out of these entities.
Very hard to tell without seeing the queries that will run against that table.
My main entity in this model is Job, it contains Client, RoomNumber, Hookers, Services,... So one query would be which materials are used mostly and how often (condoms, lubricants, ...).
I have two more 'connection tables' which connects Hooker with Job and Service with Job (each job can containt more Hookers and more Services).
I hope I'm not too vulgar, but building an Information System for a brothel is a lot of fun and me and my girlfriend had a lot of laugh about it and some really kinky ideas 😛
May 31, 2009 at 7:23 am
_simon_ (5/31/2009)
My main entity in this model is Job, it contains Client, RoomNumber, Hookers, Services,... So one query would be which materials are used mostly and how often (condoms, lubricants, ...).I have two more 'connection tables' which connects Hooker with Job and Service with Job (each job can containt more Hookers and more Services).
Still very hard to tell. Index design depends mostly on the queries that will be run against the table. No point indexing something that won't be queried.
On a many-to-many join table (connection table), I would normally start with indexing the two key fields (often done as a pk) and then put another index on whichever column is the second one in the pk, then adapt from there based on usage and performance.
I hope I'm not too vulgar, but building an Information System for a brothel is a lot of fun and me and my girlfriend had a lot of laugh about it and some really kinky ideas 😛
Each to their own. I'm building a galactic freight database to use for examples. It's more interesting than the standard banking, school, book shop type databases that most people build for test systems.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 1, 2009 at 5:09 am
We now now a unique constraint won't work for the Customer table - they're all named John. :w00t:
June 1, 2009 at 6:25 am
Scott Coleman (6/1/2009)
We now now a unique constraint won't work for the Customer table - they're all named John. :w00t:
lol:
(IS for brothel)
i saw that too, and was wondering how to get a job like that...probably has some great benefits.
Lowell
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply