473,324 Members | 2,196 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Conceptual question on table design

patjones
931 Expert 512MB
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
Oct 14 '11 #1

✓ answered by 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.

6 1625
NeoPa
32,556 Expert Mod 16PB
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.
Oct 15 '11 #2
Mihail
759 512MB
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.
Oct 15 '11 #3
TheSmileyCoder
2,322 Expert Mod 2GB
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.
Oct 16 '11 #4
patjones
931 Expert 512MB
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
Oct 17 '11 #5
NeoPa
32,556 Expert Mod 16PB
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.
Oct 17 '11 #6
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.
Oct 21 '11 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Dan Williams | last post by:
I'm trying to do a simple alteration to the table design of one of our SQL 2k tables, simply changing an identity row so that its not 'not for replication', and its taking absolutely ages to do so,...
1
by: mksql | last post by:
As an example, I am building an authentication mechanisim that will use data in the 3 left tables to determine rights to objects in a destination table, diagrammed below. In this structure,...
2
by: deko | last post by:
This may be an easy question, but for some reason the multiple table design idea is throwing me. I'm trying to avoid using one large, wide table - so I've got multiple tables that hold different...
8
by: Stewart Allen | last post by:
Hi Just asking for ideas on table design. The design I have is as follows: *tblBuildData* BuildID (PK) AutoNumber ManufactureDate SerialNumber
5
by: BerkshireGuy | last post by:
Hello everyone, I want to create an employee license plate database and need help with the best table design. I was thinking three tables: 1) tblEmployees EmployeeID EmployeeName
6
by: MLH | last post by:
If I open an A97 table, resort its key-field to descending order and attempt to close the table, A97 asks me if I wish to save the table DESIGN? Now really, I don't think the table design is being...
4
by: lorirobn | last post by:
Hello, I'd be curious to hear other thoughts on my database and table design. My database is for 'space use' in a lodging facility - will hold all spaces, like rooms (lodging rooms, dining...
3
by: shahram.shirazi | last post by:
Hi guys, I was wondering if someone could help me a bit here. Im trying to desing an electronic register system for a school. In terms of the table design, I obviously need a Student Details...
1
by: keliie | last post by:
I have a relatively simple (I assume) issue which I am at a complete loss to address. My issues is: I want to populate fields in my tables with summary data from the same table. Let me explain: ...
0
by: jsimone | last post by:
This question is about DB2 table design and performance. We are using DB2 UDB Enterprise 8.2 on Linux. We have 6 tables in a parent-child (one-to-many) relationship with each other. Each...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.