January 9, 2009 at 10:41 am
Hello room,
I need a help in my logical database design.
Can someone give me some hints on the relationship that my db design can meet a minimum BCNF?
Business roles:
1.A user can have more than one Roles.
2.A user can access more than one Sites.
Enclosed is my scripts:
--- CREATE ROLE Tables
CREATE TABLE dbo.ROLES (
RoleIDsmallintNOT NULL,
RoleNamechar(75)NOT NULL,
RoleDescriptionvarchar(150)NULL
)
GO
ALTER TABLE dbo.ROLES ADD CONSTRAINT [PK_RoleID] PRIMARY KEY CLUSTERED ([RoleID])
GO
--- CREATE Users Table
CREATE TABLE dbo.Users (
UserIdsmallintNOT NULL,
UserFirstNamechar(35)NOT NULL,
UserLastNamechar(35)NOT NULL,
CompanyNamechar(50)NULL,
Emailnvarchar(150)NOT NULL,
ContactNumbervarchar(25)NULL,
RoleIDsmallintNOT NULL FOREIGN KEY (RoleID) REFERENCES dbo.ROLES(RoleID)
)
go
ALTER TABLE dbo.User ADD CONSTRAINT [PK_UserId] PRIMARY KEY CLUSTERED ([UserId])
go
--- CREATE LINKS Table
CREATE TABLE dbo.Links(
LinksIdsmallintNOT NULL,
LinksNamevarchar(200)NOT NULL,
LinksDescriptionsvarchar(250)NULL
)
GO
ALTER TABLE dbo.Links ADD CONSTRAINT [PK_LinksId] PRIMARY KEY CLUSTERED ([LinksId])
GO
--- CREATE Sites Table
CREATE TABLE dbo.Sites (
SiteIDsmallintNOT NULL,
SiteNamechar(50)NOT NULL,
LinksIdsmallintNOT NULL FOREIGN KEY (LinksId) REFERENCES dbo.Links(LinksId)
)
GO
Thanks for the help.
Regards,
Edwin
January 9, 2009 at 12:02 pm
Here's my crack at it:
you need some tables...
user_roles that stores the relationship between users and roles (2 columns userid and siteid)
site_links '' '' '' sites and links
user_sites "" "" "" users and sites.
There should be nothing in your table that doesn't completely depend on the primary key.
~BOT
Craig Outcalt
January 9, 2009 at 12:21 pm
You should also correct the syntax of you create scripts. It's "create table ", not the other way around.
On the normalization, you will need the join tables already mentioned. You might also want to look into a natural primary key, instead of a surrogate key, if you really want BCNF.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 9, 2009 at 12:39 pm
GSquared (1/9/2009)
You might also want to look into a natural primary key, instead of a surrogate key
*high five*
A Man after my own heart. Surrogate keys are the devil! I'd go with a full row composite key before resorting to that devilish deviltry.
and I forgot to mention there should be Foreign Key constraints on the tables from my first post so that nothing exists in them that do not exist in the other tables.
~BOT
Craig Outcalt
January 9, 2009 at 12:49 pm
Hi SQLBOT and GSquared,
Many thanks for your advice and help.
Enclosed are my final database scripts:
--- CREATE Tables Roles
CREATE TABLE dbo.ROLES (
RoleIdsmallintNOT NULL,
RoleNamechar(75)NOT NULL,
RoleDescriptionvarchar(150)NULL
);
GO
ALTER TABLE dbo.ROLES ADD CONSTRAINT [PK_RoleID] PRIMARY KEY CLUSTERED ([RoleID])
GO
--- CREATE Table Links
CREATE TABLE dbo.Links(
LinksIdsmallintNOT NULL,
LinksNamevarchar(200)NOT NULL,
LinksDescriptionsvarchar(250)NULL
);
GO
ALTER TABLE dbo.Links ADD CONSTRAINT [PK_LinksId] PRIMARY KEY CLUSTERED ([LinksId])
GO
--- CREATE Sites Table
CREATE TABLE dbo.Sites (
SiteIdsmallintNOT NULL,
SiteNamechar(50)NOT NULL,
LinksIdsmallintNOT NULL FOREIGN KEY (LinksId) REFERENCES dbo.Links(LinksId)
);
GO
ALTER TABLE dbo.Sites ADD CONSTRAINT [PK_SiteID] PRIMARY KEY CLUSTERED ([SiteID])
GO
--- CREATE Table Users
CREATE TABLE dbo.Users (
UserIdsmallintNOT NULL,
UserFirstNamechar(35)NOT NULL,
UserLastNamechar(35)NOT NULL,
CompanyNamechar(50)NULL,
Emailnvarchar(150)NOT NULL,
ContactNumbervarchar(25)NULL,
RoleIDsmallintNOT NULL FOREIGN KEY (RoleID) REFERENCES dbo.ROLES(RoleID),
SiteIDsmallintNOT NULL FOREIGN KEY (SiteID) REFERENCES dbo.SITES(SiteID)
);
GO
ALTER TABLE dbo.Users ADD CONSTRAINT [PK_UserId] PRIMARY KEY CLUSTERED ([UserId])
GO
--- CREATE Table User_Roles
CREATE TABLE dbo.UserRoles (
UserIdsmallintNOT NULL,
SiteIdsmallintNOT NULL
);
GO
--- CREATE Table Site_Links
CREATE TABLE dbo.SiteLinks (
SiteIdsmallintNOT NULL,
LinkIdsmallintNOT NULL
);
GO
--- CREATE Table UserSites
CREATE TABLE dbo.UserSites (
UserIdsmallintNOT NULL,
SiteIdsmallintNOT NULL
);
GO
Have a good weekend ?
Regards,
Edwin
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply