Should I avoid 1-to-1 relationships?

[emorris1000]

Howdy
I have been working on a database for a while now and just did something that made my life temporarily easier, but I feel might bite be in the butt later on. Everything was backed up before I did this (at least I've learned that much)

I have a table called tGeneralFilm that contains a bunch of properties for blown films. And I mean a BUNCH. You have process data describing how it was made, and some property data describing what it looks like, and a handful of identifiers (different workgroups use different IDs for the material).

With all of these combined we have ~100 different properties per record. This was a monstrous table, wider than it was tall. So I decided to split it into three tables. One for Process, one for Property, and one for the different identifiers (join table).

This made my life easier while I built it, but in complete fairness the original table was first normal. No duplicate data anywhere, no blanks anywhere (ignoring the places where I was having trouble finding the data. The data definitely existed) The only significant problematic structural issue that this dealt with was that there was a Process-"Guage" and a Property-"Guage", which was confusing in the single table.

So, I took a first normal table and split it into 3 tables with one to one relationships (procces and property related to the join table). I know this will make some queries more complex (not too bad), and I don't think I'm going to get any performance gains on it, but I also don't think I'm getting any performance losses either?

Anyways, any thoughts on doing stuff like this to make unweildy tables a little more manageable?

 

[Galaxiom]

The properties should be moved into a related table as multiple records instead of multiple fields in the main table.

One field holds the key to the record in the main table. Another holds the ID of the property while another holds the value. The PropertyID and PropertyName are held in a lookup table.

There may be other fields in the lookup table such as UnitID to record the units used to measure the property.

This structure allows new properties to be added by appending them to the property lookup table. What you have now requires the tables and forms to be modified when new properties are added.

BTW It would be good to avoid using the term "property" at all since it is a very important term in databases. Most definitely don't use the naked words Property or Properties as they are reserved.

 

[Pat Hartman]

The structure suggested by Galaxiom is the one I would choose also.

1-1 relationships are extremely rare. The reason that most people make them is an invalid one though. You don't make a 1-1 because you have too many columns to comfortably fit into a single table. If you find yourself in this situation, you almost certainly have a normalization problem and need to rethink the schema.

Valid reasons for making a 1-1.
1. Security. You have some extra sensitive data and are not using a RDBMS that lets you secure individual columns so you move the sensitive columns to a separate table.
2. Efficiency. You have large memo fields that are queried only infrequently so you move them to a separate table.
3. SubClasses. Used when you have an entity that has a large variation in the columns required. For example, Students, Teachers, Guardians belong to the Class - people but rather than store all the attributes for all subclasses in tblPeople, you store only the common attributes and then create three related tables. One for each subclass to hold the unique attributes.

 

[ButtonMoon]

emorris,

Your design sounds perfectly reasonable. When you implement a foreign key constraint between two tables that share the same key then that's really one-to-zero/one (sometimes called Z-cardinality) and not strictly one-to-one. A strict one-to-one relationship constraint between two tables is usually impossible to enforce in SQL-based databases unless you disable the constraint temporarily when you update the tables. That's because the SQL model doesn't allow multiple tables to be updated simultaneously.

Z-cardinality is not the same as 1-1 because one side of the relationship is optional, which is exactly the reason why a table might be split in that way. If all the attributes are mandatory for every row then they generally ought to be in the same table.

I recommend you try to avoid the approach Galaxiom suggested and for some very good reasons. The idea of such a "properties" table is a pretty well-known anti-pattern sometimes referred to as EAV. One obvious disadvantage of that approach is that when you create a "properties" table every "property" is forced to have the same datatype(s). People using EAV will typically pick some generic lowest common denominator type such as a very long string. Not only do you lose the benefits of sensible typing you also have to apply the same constraints to every row, so any referential integrity or other integrity constraints you wanted to enforce may have to be compromised or left out altogether. EAV also makes it extremely difficult or impossible to index a table effectively and to make efficient use of the data in queries and storage. Think very, very carefully before you go down that path.

It's perhaps worthy of a separate topic in it's own right but I'd also like to offer a different point-of-view to Pat's suggestion that 1-1 relationships are "extremely rare". Z-cardinality and/or strict 1-1 relationships are fairly commonplace in reality. In accounting and ERP (accounting ledgers), commerce and finance (party/counterparty relationships, multi-legged instruments), legal contracts and especially in HR systems, CRM or other systems dealing with records of individuals. The applicability of 1-1 type relationships for supertype / subtyping has already been mentioned by Pat. Supertype / subtype patterns are extremely common. Just take a look in any good book on data modelling; they are chock-full of examples.

In the database, whenever you implement a 5th Normal Form or 6th Normal Form model you are bound to have plenty of 1-1 / Z-cardinality type relationships.

Security is by itself not a very good reason to split a table in my opinion. If your DBMS doesn't support column-level security then choose a better DBMS! All the ones I can think of support column-level security.

 

[nanscombe]

A property table where every property is forced to have the same datatype? Only if you have one property field.

I tended to use a single table with 2 available text fields, 2 numerical fields and possibly a Date/Time field and use the fields appropriate to the type of data it was storing.

tblLookup
ID (auto)number Long PK (I always had several instance so I generated my own Long PKs)
parentId number - In case I want to do a recursion
luType Text (or number) - type of data
luText Text - Text value (if needed)
luText2 Text - 2nd Text value (if needed)
luNumber Long - Numeric value - (if needed)
luNumber2 Long - 2nd numeric value (if needed)
deleted Yes/No - Has the value been marked deleted (I didn't physically delete records)

I then created a series of queries each pulling out the values for datatype.

I discussed this in, and created a demo, another thread.

 

[emorris1000]

The properties should be moved into a related table as multiple records instead of multiple fields in the main table.

There's no real reason to go EAV with this. Every record has the same properties.

I remember one database I built that contained a bunch of recipes. There were 2-3k possible ingredients but only 4-8 were used for each item. This system needed an EAV design (or at least its the only solution I could figure out).

The system I am describing is not jagged, each record has all the same fields/properties as the next record.

edit: I got schooled on EAV early on in a Stack OVerflow thread where I suggested it (without knowing its name), I must have gotten -20 votes in the first day. Good explanations as to why though.

 

[The_Doc_Man]

From the viewpoint of practicality, the only reasons I have EVER had to form a 1/1 table was a security issue, but then again, I'm on a Dept. of Defense site where security rules get rather complex very quickly.

On the other hand, the idea that supplemental data is optional leads to treating the second table as a child table where one/many includes one/none - as described in the case mentioned above as Z-cardinality. I've had that come up many times. The key is to not implement it as 1/1, but as 1/many. If it happens that you never implement more than one record in the "child" table for a record in the "parent" - Access won't care at all. Behaves just fine.

 

[ButtonMoon]

On the other hand, the idea that supplemental data is optional leads to treating the second table as a child table where one/many includes one/none - as described in the case mentioned above as Z-cardinality. I've had that come up many times. The key is to not implement it as 1/1, but as 1/many. If it happens that you never implement more than one record in the "child" table for a record in the "parent" - Access won't care at all. Behaves just fine.

The essential point about implementing 1-1 or 1-0/1 cardinality accurately is that you have a uniqueness constraint on the referencing foreign key. If you implement such a uniqueness constraint than there can never be more than one referencing row, thus 1-0/1. It is only a 1-many relationship if you omit the uniqueness constraint and thereby incorrectly allow multiple rows where business rules specified a maximum of one (in which case you would probably also be violating 3rd Normal Form).

I'm not sure exactly what your post means but it seems like you are implying that 1-many constraints are the equivalent of 1-1. They are not.

 

[The_Doc_Man]

I'm not sure exactly what your post means but it seems like you are implying that 1-many constraints are the equivalent of 1-1. They are not.

No, indeed they are not. However, the problem is that if you do a 1-1, the default relationship when you display them would suppress the "parent" record if there was no "child" record (even though we were talking about only one possible child.) I.e. inner join vs. left-or-right outer join defaults. If you are experienced enough to manipulate the relationships, your 1-1 case could still be made to work correctly, but if not, then treating a 1-0/1 case works better if you treat it as a 1-many where "many" includes 0.

 

[ButtonMoon]

No, indeed they are not. However, the problem is that if you do a 1-1, the default relationship when you display them would suppress the "parent" record if there was no "child" record (even though we were talking about only one possible child.) I.e. inner join vs. left-or-right outer join defaults. If you are experienced enough to manipulate the relationships, your 1-1 case could still be made to work correctly, but if not, then treating a 1-0/1 case works better if you treat it as a 1-many where "many" includes 0.

Now I get what you are saying. To be clear, I was talking about data integrity constraints whereas you are referring to the weird UI "features" that in Access also go under the name of "relationships" - an irrelevance really. The default join clause used in the Access UI has absolutely nothing to do with sensible database design (which is what I understand to be the subject of this forum and this thread).

 

[The_Doc_Man]

If the design is to be implemented in Access, you will need those relationships and the catch is going to be the way you implement them. INNER JOIN and OUTER JOIN are facts of life in implementation regardless of the base design.

However, I agree that in a "pure" sense, the 1/1 case (where one side is sparse) is valid and even makes sense. Sadly for Access, the devil is in the details.

 

[ButtonMoon]

If the design is to be implemented in Access, you will need those relationships

Certainly not so for anyone even moderately competent at SQL. And a person so inexperienced as to "need" such a crutch probably ought not to be designing a database. Inexperienced developers are better off using other tools anyway.

 

[The_Doc_Man]

Speaking so disparagingly about people who need crutches... tsk tsk tsk.

In terms of Rapid Application Development, what other tools would you use besides Access? I used to like Paradox, but Borland "screwed the pooch" when they converted Paradox from DOS to Windows environment. They got it wrong. You could try ORACLE Tools, but I've not heard great things about it. Then there is PeopleSoft, which in my indirect experience from projects out at work seems to be an excellent tool for selling hardware (because your little one-lung workstation won't be able to keep up with the ever-increasing demands of Peoplesoft).

As to "a person so inexperienced" ... That is why we make comments such as I did in these forums - so the folks who are inexperienced can take advantage of our knowledge. Be generous with your knowledge. Incompetence can be overcome by careful instruction, example, and patience.

I don't say that we should give all that we know to others - after all, we are not paid to be on-line instructors - but what is the purpose of the forum EXCEPT to share knowledge and experience?

 

[ButtonMoon]

In terms of Rapid Application Development, what other tools would you use besides Access?

I wasn't actually criticising Access in general. The "crutch" I was referring to was simply the Access feature which makes a "relationship" become a default for the joining condition when you design a query in the Access UI. That feature is unnecessary and can be ignored if you write your own SQL. On that basis I was suggesting that your caution about 1-1 relationships is unjustified - it really doesn't matter what the "default" joining condition is if you are competent enough to write a SQL query.

Re Access in general, I have said before that it isn't a good environment for a less experienced person to learn about database principles. There is too much in Access that misleads or obscures important issues of database design and management (the daft "relationships" UI being one example). Students learning about database concepts should avoid Access until they gain some knowledge and experience. That isn't a criticism of Access as a RAD tool, just as an educational tool.

 

[The_Doc_Man]

Interesting. From my viewpoint, declaring a relationship explicitly (and therefore having it automatically implemented/enforced in queries) isn't a bad thing.

"When you write your own SQL" ... But a quick and dirty way to write your own SQL is to use the query-builder grid first, then switch to SQL view and see what Access thinks it should do. Just like the form, report, and table wizards, the query wizards are as dumb as a box of rocks (and I'm sure you'll give me an AMEN on that) - but the wizards make it possible to quickly "erect a scaffold" that you can then customize. I treat them as a starting point, not the end-all and be-all. Any tool that gives me that kind of help is my friend.

Not to mention that it is an excellent self-teaching tool for people to use the query wizard and then see what it generated in the form of SQL.

Don't get me wrong, I'm not a purist. I'm a pragmatist. I've been using Access since version 2.0 of Office on Windows 3.1 (Oops, did I just give away my age...?) I have found it to be an excellent way for someone who can't afford a server in their own home to do something productive with "real" databases - from the practical viewpoint. I know that it is not quite the same as ORACLE or SQL Server or Informix or DB II or what-have-you.

Think of it this way. In the final analysis, you've got a file container that the database manager application manages for you. You don't need to know the details of the container's management. If you are an ordinary user, you don't even KNOW about the size or number of containers. You can put stuff in and get stuff out using SQL or form/report applications that use SQL behind the scenes. How is Access different from ORACLE, SQL, etc when you look at it that way?

You can say that normalization beyond 3NF is difficult or impossible using Access, but there are folks on this forum who have done exactly that. You can say that you need a more robust database engine, but if you ever get the funding to build out your own SQL engine, you can do an "up-convert" of your Access database (assuming you have the ODBC modules you need) and the performance boost associated with a dedicated server. I think Access is an excellent starting point for learning about databases.

 

[ButtonMoon]

the wizards make it possible to quickly "erect a scaffold" that you can then customize. I treat them as a starting point, not the end-all and be-all. Any tool that gives me that kind of help is my friend.

Pretty much every other DBMS gives you that kind of help, and does a far far better job of it than Access.

Not to mention that it is an excellent self-teaching tool for people to use the query wizard and then see what it generated in the form of SQL.

Except that the queries it produces will in many cases use Jet/ACE specific dialect of SQL that is incompatible with everyone else. All SQL DBMS have some proprietary elements but no other software that I know of works so hard to be incompatible with the rest of the world.

I have found it to be an excellent way for someone who can't afford a server in their own home ... if you ever get the funding to build out your own SQL engine

Last time I checked SQL Server, Oracle, DB2, MySQL, ProstgreSQL were all cheaper than Access, i.e. you can download, install and run them for production use for free. You should also be able to do more with them on cheaper hardware than you would need to run MS Office applications. Even the Developer Edition of SQL Server (functionally equivalent to Enterprise Edition) is probably cheaper for most people than Access for individual development and learning purposes.

You can put stuff in and get stuff out using SQL or form/report applications that use SQL behind the scenes. How is Access different from ORACLE, SQL, etc when you look at it that way?

In a thousand different ways. But this thread is already too off-topic.

 

[nanscombe]

If you were familiar the various flavours of SQL for Oracle, SQL Server etc why would you be using the Access query wizards instead of Pass Through queries anyway?

You may be able to download MySQL for free for personal use but I don't think that any commercial usage will be free.

MySQL Editions

MySQL Community Edition is the freely downloadable version of the world's most popular open source database.
...
Commercial customers have the flexibility of choosing from multiple editions to meet specific business and technical requirements:
...

			MySQL Standard Edition	MySQL Enterprise Edition	MySQL Cluster Carrier Grade Edition
[B]Annual Subscription[/B]	USD 2,000		USD 5,000			USD 10,000

 

[stopher]

This made my life easier while I built it, but in complete fairness the original table was first normal. No duplicate data anywhere, no blanks anywhere (ignoring the places where I was having trouble finding the data. The data definitely existed) The only significant problematic structural issue that this dealt with was that there was a Process-"Guage" and a Property-"Guage", which was confusing in the single table.

If your structure is in 3NF and there is no compelling technical reason to do otherwise then leave your structure in the normalised structure i.e. stick with a single table. 100 fields should not be an issue since you only deal with it at design. You can easily view sub groups of fields by creating appropriate queries.

The only concern you have raised is about "guage" confusion. Isnt this just a naming thing. Or is there more to this than you've suggested? Maybe you could explain more about you reasons for wanting to split the table.