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 |
|
iwaters
Starting Member
13 Posts |
 Posted - 2003-07-26 : 05:37:20
|
| I have a table which has over 40 attributes, these will be populated by a web based form. Now I also have about another 20 attributes which relate to the first 40, however, these will not be entered at the same time. I have put these other attributes in a new entity because I dont want to mark them as allowing NULLs in the first entity. This now creates a one-to-one relationship between the two entities. Is this bad design and should I move the atrtibutes into one table and allow NULLs?Any thoughts appreciated. |
|
|
smccreadie
Aged Yak Warrior
505 Posts |
Posted - 2003-07-26 : 07:04:11
|
| A one-to-one relationship is not the worst thing out there. Most will say that they should be combined to a single entity. I've done a similar design a couple of times and it works OK.If there is a way to redesign it to avoid this, then do it, otherwise you can make it work. |
 |
|
|
r937
Posting Yak Master
112 Posts |
Posted - 2003-07-26 : 09:58:21
|
this type of design is produced by one of the levels of normalization -- fourth? fifth? i forgetit has advantages, one of which is that no nulls are requiredthat could, i suppose, be offset by the additional column(s) in the extra key"however, these [other attributes] will not be entered at the same time" implies that the relationship is not one-to-one where every row has a guaranteed match, but one-to-zero-or-one (or zero-or-one-to-one if you prefer  )as you develop queries against this structure, sometimes you'll want the additional columns, sometimes you won'tif you think about how to combine the separate tables, happily, there is a choice -- an inner join or an outer joinnow imagine putting those two different joins into viewsthe view of the inner join is the same as a query on the single table (which itself could be made into a view) with a WHERE clause specifically filtering out rows with nulls the view of the outer join is the same as a query (or view) on the single table without a WHERE clause, which returns rows both with and without nullsso now let's run a query on the single table, and let's say we want some columns from both sets of attributesin order to select rows from the single table which have no nulls, what would make the performance fly? an index on the column(s) from the second table, right? otherwise a table scan is required, right? and of course the presence of nulls doesn't matter, since those don't get indexed, right?if i sound unsure, i amhow would that performance compare to the performance of joining separate tables?i'm not a DBA so i'm curiousrudy |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-28 : 10:05:15
|
| Why are Nulls so dis-ed?And I don't think it fits in any normal form...Once you get pasted 4th ( multi valued facts...composite keys, ect)Fifth (and I've never seen a system implement it...and most hanging around a mixture of third and fourth) deals with the (and I'm quoting now) the "reconstruction of data from smaller peices that can be maintained with less redundancy"Well I guess that's not true...Usually a "type" field that descibes what in a column, I guess could be considered 5th...But I've seen that get waaaaay out of hand....I guess it comes down to, whatever works best for you...I'm going with a single tableMOOBrett8-) |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-07-28 : 10:16:12
|
| Here's my rule of thumb. clip and save, attach it to your fridge with a magnet !!-----------------------If you have a table with this format:PrimaryKey,Attribute1,Attribute2,..etc...OptionalAttribute1,OptionalAttribute2,OptionalAttribute3,OptionalAttribute4and your rule is: If OptionAttribute1 is NOT Null, then the other optional attributes cannot be Null either -- put them in a seperate table.Example: if you have a table of Employees:EmpID,Name,Dept,Salary,LogInName,LogInDomainLogInPassword,LogInPermissionLevelmaybe only half the employees in your company have access to computers, so only half have Log-In's. EmpID to LogInName is a 1-1 relationships -- they could potentially all live in 1 table. But because you have other attrbutes dependant on the LogInName, and will all be NULL if LogInName is Null (and NEED TO BE null -- your data is not correct if it's not), put them in another table with a 1-1 relationship.That's just my take ....- Jeff |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-28 : 10:34:45
|
quote:
But because you have other attrbutes dependant on the LogInName, and will all be NULL if LogInName is Null (and NEED TO BE null -- your data is not correct if it's not), put them in another table with a 1-1 relationship.
If EmpId is the Key, and you're saying that other fields are dependant on the LoginId, then absolutley (on the rocks please)Otherwise you'd be breaking 3rd...(Again with the quotes)"Third normal is violated when a non-key field is a fact about another non-key field"Brett8-) |
|
|
|
JustinBigelow
SQL Gigolo
1157 Posts |
Posted - 2003-07-28 : 10:36:37
|
quote:
MOOBrett
Uh oh, Mr. Mist has Brett mooing now too. Maybe mad cow disease is spreading through the forums. And just to contribute something to the topic at hand... I've started warming up to one-to-one tables. I'm potentially starting another application that I have to build from the ground up. This will be the third time there will be a locations table, customers table, users table, etc etc... and I'm willing to bet if there is another job after this one the same basic table structure will exist/be required. An example of something unique about this job is that I'll have to track latitude and longitude for the buildings in the locations table. This is rare requirement so I won't want it to be part of a common table structure that I re-use in other projects, and hence a one-to-one (or one-to-zero if the building is currently only a speculative location) seems natural. I'm trying to get to the point where I can re-use alot of my code. A one-to-one/zero with a table of common fields joined to a table of application domain specific fields is analagous to inheritance while staying within the relational world. Yeah, yeah, inheritance and that kind of stuff is for those touchy-feely OO languages and not T-SQL but I'm lazy and tend to re-invent the wheel too many times.I haven't yet gone down the one-to-one in the name of code re-use path yet, I'm still planning. If I have made an oversight as to why this is bad from a relation stand point I'd like to hear it. I wouldn't want to have alot of data structure problems down the line.m2c,Justin"Look at it out there orbiting like it's so cool, we will rule it with an army of replicants!" |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-07-28 : 10:44:29
|
quote:
Originally posted by X002548
quote:
But because you have other attrbutes dependant on the LogInName, and will all be NULL if LogInName is Null (and NEED TO BE null -- your data is not correct if it's not), put them in another table with a 1-1 relationship.
If EmpId is the Key, and you're saying that other fields are dependant on the LoginId, then absolutley (on the rocks please)Otherwise you'd be breaking 3rd...(Again with the quotes)"Third normal is violated when a non-key field is a fact about another non-key field"Brett8-)
yep, empID is the key -- sorry, I forgot to mention that !- Jeff |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-28 : 10:48:32
|
| Justin,Mr. Mist likes to bivine Moo (a lowing sound...)My are because of My (irrational) Own Opinions...But where are you going with your post...sounds like you're talking about code re-use..What code? DB Design...Why is 1-1 going to give you any leverage?I would think it's a disadvantage...Brett8-) |
|
|
|
JustinBigelow
SQL Gigolo
1157 Posts |
Posted - 2003-07-28 : 11:52:14
|
quote:
Originally posted by X002548
Justin,But where are you going with your post...sounds like you're talking about code re-use..What code? DB Design...Why is 1-1 going to give you any leverage?I would think it's a disadvantage...
Yeah, I'm talking about code re-use in the DB. My thought is that if Table X is gauranteed to almost always have fields 1 through 8 why not use the same table over and over again with a one-to-one join to a second table that includes application specific fields. Here are the possible advantages I see from this (using a locations table as an example)...1. There is a chance that a join table would not be needed if all that is required is name, ship address, bill address, or whatever else you consider generic information.2. Continually reusing the same structure will (if monitored) result in knowledge of how best to optimize indexes. You can start the application with a highly optimized index configuration instead of gather metrics and refining X months into the life of the app.3. Can avoid the use of nulls if desired.4. If you force all access by the client app thru stored procedures the join between tables is transparent.Possible disadvantages...1. Join required if application specific data is requested by the client.2. More complex stored procedure required to join or the need for a view, either one resulting in marginally higher maintenance costs (i.e. permissions management on the extra structures, etc...)3. Possible extra index(es) required to support the join between the tables.Are the extra indexes, structures, and time needed by the DBA/developer worth the time savings to the client developer? Don't know yet. Is the DBA's life made easier by being able to extend verified well tuned structures by adding another table and tweaking the sprocs? Don't know yet.MOO isn't appropriate for me, how about MOQ (my own questiosn)  Justin"Look at it out there orbiting like it's so cool, we will rule it with an army of replicants!" |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-28 : 12:00:10
|
| Ahhhhhhhhh..the silver bullet approach...Never seen it work, and you might do a dis-service to a good relational design by try to artificially "make life easier".Once you strat down that path, I would imagine that you start making exceptions to make sure you float that model, because then you have to, beacuse there's no turning back.And besides, most data is modeled the same way over and over, but you can gaurentee...there will always be minor differences..ok, now for a big..MOOBrett8-) |
|
|
|
Tim
Starting Member
392 Posts |
Posted - 2003-07-28 : 21:31:25
|
| I like Jeffs advice.Another reason in favour of your approach is that by separating the attributes and therefore avoiding the nulls, you also avoid complications of including null values when summarising either table. |
|
|
|
Scott
Posting Yak Master
145 Posts |
Posted - 2003-07-29 : 04:08:55
|
| What about a bit of perspective. Isn't everything we do a compromise performance/reusability/code structure.Most real world applications sit somewhere between 3rd and 4th normal simply because the theory of normalisation is nice and gives us all a nice fuzzy warm feeling inside but the reality is that in most cases anything past 4th normal is simply impractical.My take is that most applications developed have limited users and although everyone likes to talk about terabit size databases your AVERAGE web/intranet application sits at way under a gig. Web applications often deal with very few entities, at least the type I work on, have limited users and databases that rarely exceed a few hundred megs. Often entity data is captured over a period of time and for me NULLS are a greater evil than a couple of 1-1 or 0 relationships.Certinaly this is not correct to the letter but is definitly not a cardinal sin with the compromise in favor of easier and perceviably more logical design which takes a minor performance hit. |
|
|
|
|
|
|
|
|
One-To-One - Bad Design?