sql server programming: Best SQL design? -- sql server programming

Re: Best SQL design?

Arnie Rowland
11/11/2006 1:49:02 PM

The more important design question involved the existing three tables. The
'design' of having three almost identical tables (for "Managers, Consultants
and Admin") is a bad design.

There is most likely a need for ONE table, with an additional column
identifying the 'role' of "Manager", "Consultant", or "Admin" (and other
columns as necessary). Then the question about the blog table would be a
'non-starter'.

I suspect that this bad design decision will 'bite' you in other ways before
this project's life cycle is over.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf

[quoted text, click to view]

Re: Best SQL design?

--CELKO--
11/11/2006 4:03:27 PM

Can I assume that you fired the moron who put all those IDENTITY
columns on the tables or at least made him read book on RDBMS? Did
careful research show that people often have a first name that is
THIRTY characters long? Wow! CHAR(35) is the recommended size for a
full address line on a mailing label.

The design problem is called attribute splitting - you have made
tables for what should be an attribute. Do you put Male and female
personnel in two tables? No. Same idea.

CREATE TABLE Bloggers
(blogger_nbr VARCHAR (20) NOT NULL PRIMARY KEY,
blogger_type CHAR(1) DEFAULT 'C' NOT NULL
CHECK (blogger_type IN ('C', 'M', 'A')),
first_name VARCHAR (30) NOT NULL,
...);

CREATE TABLE Blogs
(blogger_nbr VARCHAR (20) NOT NULL
REFERENCES Bloggers(blogger_nbr),
posting_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (blogger_nbr, posting_date),
blog_subject VARCHAR(50) NOT NULL,
blog_message VARCHAR(2000) NOT NULL,
..);

If you need to add special data, then make (blogger_nbr, blogger_type)
unique and reference it in those tables.

Best SQL design?

Cipher
11/11/2006 4:11:44 PM

I'm currently building an ASP.Net application that contains three types of
users (Managers, Consultants and Admin) whose related data is stored in
three simple tables:

CREATE TABLE dbo.Managers(
ManagerID int IDENTITY (1, 1) NOT NULL,
ManagerNumber varchar (20) NULL,
FirstName varchar (30) NOT NULL,
....
) ON [PRIMARY]
GO

CREATE TABLE dbo.Consultants(
ConsultantID int IDENTITY (1, 1) NOT NULL,
ConsultantNumber varchar (20) NULL,
FirstName varchar (30) NOT NULL,
....
) ON [PRIMARY]
GO

CREATE TABLE dbo.Admins(
AdminID int IDENTITY (1, 1) NOT NULL,
ConsultantNumber varchar (20) NULL,
FirstName varchar (30) NOT NULL,
....
) ON [PRIMARY]
GO

Now, I need to add a diary/blogging page for each of these users and I'm
currently thinking of creating one table, called Blogs, that will store this
data. The only problem is when retrieving the associated data for a user I
need a way to identify their group (ie Manager, Consultant, Adim) and then
adjust the join to their related table. For example, this is the table I'm
considering...

CREATE TABLE dbo.Blogs (
BlogID int IDENTITY (1, 1) NOT NULL ,
UserID int NOT NULL , --Associated ID
from either Managers, Consultants or Admins table
UserGroupID tinyint NOT NULL , --0=Managers,
1=Consultants, 2= Admins
BlogSubject varchar(50) NOT NULL ,
BlogMessage varchar(2000) NOT NULL
) ON [PRIMARY]
GO

Is this the best design decision, as far as performance and following SQL
best practices goes, for achieving this functionality or would I be better
of creating a Blogs table for each user (ie MangersBlog, ConsultantsBlog,
AdminBlog) and then letting the middle tier determine the user type and then
calling the appropriate data retrieval function?

Thanks in advance

Re: Best SQL design?

Cipher
11/11/2006 4:42:47 PM

The only difference is the type of data that is being tracked for each
group. I could have placed them all in one table (ie Users) but that would
have meant having columns which had no relevance to one or two of the user
groups.

[quoted text, click to view]

Re: Best SQL design?

Henrik Staun Poulsen
11/12/2006 11:01:19 PM

Hi,

I had the same problem. So I created one big table, and 3 views; we
could call them vwManagers, vwConsultants and vwAdmins, or you could
just reuse the old names (Managers, Consultants and Admins).

Then you can create your Blogs table without problems.
Note that this can be done on an existing system, with a piecemeal
installation.

HIH
Henrik Staun Poulsen

Cipher (remove) skrev:
[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.