Conceptual question on table design

[patjones]

Good afternoon all. This is a conceptual question on table design. I have not come across this situation so I thought I'd see what people have to say about it.

I have three tables in a twelve table database which, between the three of them, comprise 32 columns. They are joined together by a column called PIN. My conceptual issue is that the two relationships that bind the tables together are one-to-one. So, I could really put all 32 columns in a single table with PIN as the primary key. The reason I split the schema out into three tables was two-fold.

One, it seemed to me that 32 columns is a lot to have in a single table in a normalized database. Two, the divisions that I created make sense when one looks at the groupings; for example, CP_Status and CP_Number are closely related to each other in the actual business process that I'm modeling, and so are FM_Doc and FM_Amt. All four columns have a one-to-one relationship with PIN, and yet in the real world each set of columns has a different role in the business process. Does this make any sense?

Up to this point, the development process has worked fine with the split, but it does make the SQL more complicated on account of having to do the JOINs.

The database in question is a SQL Server back end/Access front end connected via ODBC. Thanks in advance for any insight.

Pat

[NeoPa]

My advice would be to store it as a single table with all the columns in it. There's nothing to stop you having multiple views of the table (virtual tables if you like) being implemented as QueryDefs. I would only consider such an approach as you describe if a large proportion of the PIN records had one or more of these separate sections completely empty. Even then I'd still be inclined to err in favour of a single table, but it would depend on context.

[Mihail]

I don't understand NeoPa's point first because I am not enough skilled in Access and second because my English.
But my concept is to model the reality as you feel it.
So, if you "feel" that more tables are better than one use more tables not only one. Any time you can "assembly" this tables, using a query (maybe a MakeTable query). Then use this "assembly" to go on. Notice please that this is MY idea, not necessary a good one.

[TheSmileyCoder]

I think it would(well could) depend on whether you have a true 1-1 or if its a 1-1(0).

By 1-1 I mean a relation in which there is always a entry in both tables, and by 1-1(0) I mean a relationship in which there MIGHT be zero entries or one entry in the second table, but never more then one.

If the first is the case, I would combine it into one table. If the later is the case, I guess I would look at how often its a 1-1 and how often its a 1-0 relation. If only 1 in 1000 records have 1-1 you could save alot of space, and traffic by creating 2 seperate tables. If its only 1-1000 that are 1-0 then you could save alot of joins by joining the tables.

I have some tables also where a few of the columns change often, while most of them remain static throughout their lifetime, but they ARE changeable. In that case I like to split of the columns that change often, because I keep historic records for each change, and this spares me having to also save the history on the columns that don't change that often.

[patjones]

Thanks for your input guys. I think for now that I'm going to leave it split, because it's not clear yet whether I'm looking at a 1-1 or 1-1(0) situation. What I was looking to find out is whether it's poor practice to have so many columns in a single table and I think that question has been answered sufficiently.

Pat

[NeoPa]

People are often inclined to think about how they would design their databases in ways that fit human thinking and abilities. Databases often don't work in similar ways. Most of the work in a database is in finding the data in the first place. My focus is generally on reducing that element of the process (hence the bias towards a single table approach), but whichever approach you take you can be sure that an RDBMS should be able to handle large numbers of columns reasonably well.

BTW That's also why I tend to err on the side of more indices for a table even though it gives a larger overhead for updates. It's worth taking a balanced view of what the db is for though. If you had some sort of logging system which was streaming data all the time, it may be worth reducing the update overhead and living with slower recall when required. Each database needs to be taken for what it is, but understanding why certain things are important, and what effect they cause, can help you make those decisions.

[RisoSystems]

A question you might consider is whether or not there is a high degree of correlation between sets of columns. By that I mean do you have a large number of records where Columns A and B (and C and D...) vary simultaneously and in the same way?

--Col 0-- --Col A-- --Col B--
505511 Apples Fruit
505512 Cars Machine
505513 Bread Grain
505514 Apples Fruit
505515 Bread Grain

That would suggest to me that a split would be to your advantage.

Obviously if you have to many splits your indexing overhead will become problematic. The one piece of data missing from your question is the overall number of records in the finished system. If you're managing a couple of thousand records, you might go one way, where if you're managing tens of thousands (or more) you might go another.