Designing this db.. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Designing this db..

Author

Topic

TazMania
Yak Posting Veteran

63 Posts

Posted - 2005-12-05 : 10:19:02

Hi :)

Sometimes you can stare blankly at a problem, well i'm just sitting here doing exactly that hehe..

I need to design a db with the following categories.
All with predefined data, so that i'm sure that there wouldn't be any duplicate data..

table1 - Userdata ( initials )
table2 - Projecttypes ( web,internal,external etc. ) - predefined data
table3 - elementtypes ( office,pc,server etc. ) - predifined data
table4 - specialtypes ( cad,sap,kalkule,br95 etc. ) - predifined data.
table5 - systemtypes ( d4info,storage etc. ) - predifend data.

My question is how can I design this database, since every user, can have more than one type.. f.ex.

User1 have good knowledge in Projecttype ( web,internal ) and elementtype ( office ) and special type ( cad,sap,br95 )and systemtypes ( none )

My first idea was to collect all data to table1, but would that be a smart idea since we are talking about +50 columns ?

Below is how far i am..

Table.User
userID;int;4;pri
userInit;varchar;4

Table.ProjectType
projectTypeID;int;4;pri
projectTypeLabel;varchar;50

table.ElementType
ElementTypeID;int;4;pri
ElementTypeLabel;varchar;50

table.SpecialType
SpecialTypeID;int;4;pri
SpecialTypeTxt;varchar;50

table.SystemType
SystemTypeID;int;4;pri
SystemTypeLabel;varchar;50

I hope that some of you guys can help my out here, please :)

Best regards
Taz

jhermiz

3564 Posts

Posted - 2005-12-05 : 10:45:49

Not a good design.

If you say each person can have more than one type then you need to store the UserID and the ObjectID in a seperate table, even if this is for multiple elements.

Here is another thing you can do...All of those categories are types: Project, Element, System, and Special types.

So instead of that many tables you create the following:

Object
-------
ObjectID
Object

Sample Data:

1 System
2 Project
3 Element
4 Special

Then you create a another table that takes a typeID and a classification ID (classification can be in itself another table). If classification becomes another table, then you merge the two tables: Object and Classification into a 3rd table ObjectClassifications.

Hope that gives you an idea.

Jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

TazMania
Yak Posting Veteran

63 Posts

Posted - 2005-12-06 : 09:52:58

Hi jhermiz

Thx for your reply

Do you mean that I should do as follows ?

Table.user
userID;int;4;pri
userInit;varchar;4

Table.ObjectType
ObjectID;int;4;pri
ObjectLabel;varchar;50

It just crossed my mind, that I would have a problem to store the user specific data ?

Since this database will have data of about 400 people.

Or maybe I dont quite follow you jhermiz

Sample data:
User1- ProjectType(web,internal);elementtype(office);special(cad,sap,br95);systemtype(none)
User2- ProjectType(external);elementtype(office,pc,server);special(cad,kalkule);system(storage)
etc..

Ofcourse the text, storage,office,pc,server would be exchanged with the corresponding id numbers of that object.

Best regards
Taz

TazMania
Yak Posting Veteran

63 Posts

Posted - 2005-12-07 : 02:12:18

Hi,

I Finally figured it out, how to do it. with the help from array I can store the user selected data in each column :)

Best regards
Taz

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources