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.
| Author |
Topic |
|
czeller
Starting Member
10 Posts |
 Posted - 2003-01-18 : 12:07:56
|
| hi all, i'm setting up a database to store user information. my problem is that there is a TON of information we're capturing from different parts of a website so i thought it would be best to have a base "Users" table to hold all the generic user info (name, address, phone, etc...), and then put all the extranneous information for each "group" of users into a different table, linked back to the Users table w/ a foreign key. something like this (generic version - i am at home and can't connect to the server these tables are on, so if needed i can repost w/ a full ddl):--generic user infoCREATE TABLE [dbo].[Users] ( [pkUserID] [int] IDENTITY (1, 1) NOT NULL , [firstName] [varchar] (20), [lastName] [varchar] (30), [city] [varchar] (30), [state] [char] (2), [zip] [varchar] (10)) ON [PRIMARY]--employee-specific infoCREATE TABLE [dbo].[Employeees] ( [pkEmployeeID] [int] IDENTITY (1, 1) NOT NULL , [fkUserID] [int] NULL , [field1] [varchar] (20), [field2] [varchar] (30), [field3] [varchar] (30), [field4] [varchar] (30), [field5] [varchar] (30)) ON [PRIMARY]--info request-specific infoCREATE TABLE [dbo].[RequestInfo] ( [pkRequestInfoID] [int] IDENTITY (1, 1) NOT NULL , [fkUserID] [int] NULL , [field1] [varchar] (20), [field2] [varchar] (30), [field3] [varchar] (30), [field4] [varchar] (30), [field5] [varchar] (30)) ON [PRIMARY]--retailer-specific infoCREATE TABLE [dbo].[Retailers] ( [pkRetailerID] [int] IDENTITY (1, 1) NOT NULL , [fkUserID] [int] NULL , [field1] [varchar] (20), [field2] [varchar] (30), [field3] [varchar] (30), [field4] [varchar] (30), [field5] [varchar] (30)) ON [PRIMARY]--wholesaler-specific infoCREATE TABLE [dbo].[WholeSalers] ( [pkWholeSalerID] [int] IDENTITY (1, 1) NOT NULL , [fkUserID] [int] NULL , [field1] [varchar] (20), [field2] [varchar] (30), [field3] [varchar] (30), [field4] [varchar] (30), [field5] [varchar] (30)) ON [PRIMARY]......something like 10 tables totalthat all seems fine to me, but the trouble comes in when i try to view the details for a given user, i'd like to be able to show what groups a user is in. the only way to do this is to join all the tables together, which performed very poorly when i tested it (is there a generally accepted max # of tables you should join?).so i thought i would make a linking table to just hold user-group information, like this:CREATE TABLE [dbo].[Groups] ( [pkGroupID] [int] IDENTITY (1, 1) NOT NULL , [groupName] [varchar] (30)) ON [PRIMARY]CREATE TABLE [dbo].[Users_Groups] ( [fkGroupID] [int] NOT NULL , [fkUserID] [int] NOT NULL ) ON [PRIMARY]so i could just join the users and users_groups tables and get all generic user info and all the groups each user is in w/o any problems.BUT! doing that now makes me do 3 things everytime a record is added to any of the tables:1) add them to the users table2) add them to whatever table corresponds to the form they just filled out3) add them to this new users_groups table (was going to handle this w/ an insert trigger on each "group" table)that 3rd step seems redundant because i already know what group they're in because of step 2! i was taught (as a general practice) that you never want to store data you can figure out using what you already have.i thought about just putting EVERYTHING into the users table, but i almost certainly would exceed the max # of bytes per row (8060). or i could simply add a field for each group into the Users table, but that just "feels" wrong from a design standpoint. but i guess i'd have to create a new table anytime a new "group type" comes along, so adding that bit field might not be so bad. i'm ignorant about indexes but from what i've read, that will probably help out a lot. if that's all i need, then i can just go back to what i started with and scrap the whole linking table (users_groups) idea and everything will be peachy. :)i'm open to any suggestions on how i could improve this design! thanks in advance for any constructive criticism, i've been reading posts on this site for a while and have learned a lot, you guys are awesome :Dchris |
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2003-01-18 : 13:17:10
|
after a quick read ... and just looking at the sample DDL ... I hope that you really aren't using FIELD1, FIELD2, FIELD3, etc for your column names ... give them descriptive names ... maybe if you described that business rules that defines the data then we could help you put together a better schema ... or maybe i'm just tired and bored and isn't this my 420th post  |
 |
|
|
czeller
Starting Member
10 Posts |
Posted - 2003-01-18 : 14:26:17
|
| no, i'm using descriptive names in the real database. sorry about the generalized ddl, i didn't have access to the server when i wrote the post so i just whipped up a sample of what i have set up. i'll follow-up w/ the full dll when i get on the server.i was just wondering if there was a general "join limit" you want to try to avoid in queries/design (i'm sure it depends on a lot of factors, but just wanted to see what sqlteam.com readers usually shoot for). and if so, if i should just add a flag to my master (Users) table to indicate each group (table) a given user belongs to in order to avoid the complex join statement.thanks! |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2003-01-18 : 16:39:59
|
| bye bye post 420anyhow it would be best to keep them in seperate tables ... think of it this way ... you want to describe the entity as its own self ... so you go with an employee type example ...the employee is not just an employee ... he is a human ... the most common factor ...so you have a Person that could be an Employee that could also be a Spouse and an Author ...so its good to have it down to that level ... it makes it more *real* ... rather than saying ya he could do this but he doesn't doesn't make a lot of sense to store information about an Author if that Person is never an author of anything ... and if he is then it would be in use ... am I making it clearer? ... brb cooking ...anyway if you have too many joins it will result in poor performance for the application ... but it just depends on what you want to display ... i'm not sure if you should have a many-to-many type table for each user and the other tables ... unless thats how the data is supposed to be ...for instance you could have a Person that has possible zero or more Roles in something ... so you would have PersonRoles that brings those two together ... since multiple people can have multiple roles and vise versa...with your example ... the users and requesters and employee would be more specific to be sub attributes of the user entity .. thus you sub-entities should reference a single user one-to-many in this case for each table ... your turn... |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-01-19 : 02:11:18
|
I sometimes wish I could do something like this in SQL:CREATE TABLE Person(fields....)CREATE TABLE Employee INHERITS Person(Employee specific fields)Similarly tables of Suppliers, Customers, etc. There is certainly a lot we can do with Relations, joins, etc. but if only it was all object-oriented <sigh>. That reminds of the rumours of Yukon being completely object-oriented  I guess everything is built in its own way, and maybe SQL wouldnt be so efficient (and as much fun) if it were all object-oriented. OS |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-01-19 : 06:11:52
|
| Mohdowais,You can achieve exactly that with SQL Server today with a simple one-to-one design and a view.There is no formal obect-orientated data model yet. And just quietly, seems to be nowhere in sight. I strongly recommend you visit [url]www.dbdebunk.com[/url].DavidM"SQL-3 is an abomination.." |
|
|
|
|
|
|
|
|
db design...joining too many tables?