473,834 Members | 1,808 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Table design question.

4 New Member
Longtime lurker... first post. These forums have already helped a lot, so thanks.
I have a design issue that is probably easy to figure out for someone with a little more DB experience than myself. Here is what I'm trying to do:
I have an app that will display several grids which consist of some columns that are specific to a given grid and some that are globally identical among the all grids. The globally identical columns will be displayed for all grids but the relative columns may or may not be a part of a given table...if they do show up in more than one grid, their values will be different. A given row may or may not show up in a given grid as well. You can think of this in terms of a jagged datacube. Here is a simplified version of the design (tableName followed by tableColumns).. .

________

Columns
ColumnName | ColumnID(PK)

Rows
RowName | RowID(PK)

Grids
GridName | GridID(PK)

Cells
CellContents | ColumnID(FK) | RowID(FK) | TableID(FK)
_______

I plan to handle the global columns by assigning a GridName of All w/ a GridID of 0. Application logic will treat cells with this GridID as belonging to all grids. This achieves 3NF, but here's the problem- and although the app logic won't allow this to happen it would be possible to duplicate a column in a grid, ie have two columns with the same ColumnName but one contains global data and the other contains data relative to the given grid. This seems like a DB design flaw to me. Any suggestions on how to tidy it up?
Mar 7 '08 #1
4 1083
jm1
4 New Member
Nothing? Surely this is an easy one... for someone.
Mar 10 '08 #2
ck9663
2,878 Recognized Expert Specialist
Try putting some sample data on those structures and paste it here. Then what you want as your output. Let's go from there...

-- CK
Mar 10 '08 #3
jm1
4 New Member
I think I've figured it out. The 'Cells' table should only be a junction table. That means CellContents gets broken out into its own table w/ an Identity value as a PK. The identity value gets inserted in place of CellContents and the idea of using 0 as an "All Grids" identifier goes out the window. Rows that were using 0 as a Grid ID get expanded into individual rows- (1 through NumberOfGrids) and all have the same CellID pointing to the appropriate row in the new table.
If that sounds garbled, its because it is. Back to the second sentence in this post. It's just a junction table. I was thinking too hard in the context of the immediate application. The new approach is more flexible for future applications.

The new design looks like this:

________

Columns
ColumnName | ColumnID(PK)

Rows
RowName | RowID(PK)

Grids
GridName | GridID(PK)

CellJunction
CellID(FK) | ColumnID(FK) | RowID(FK) | TableID(FK)

Cells
CellID(PK) | CellContents
_______
Mar 10 '08 #4
jm1
4 New Member
[quote=jm1]I think I've figured it out. The 'Cells' table should only be a junction table. That means CellContents gets broken out into its own table w/ an Identity value as a PK. The identity value gets inserted in place of CellContents and the idea of using 0 as an "All Grids" identifier goes out the window. Rows that were using 0 as a Grid ID get expanded into individual rows- (1 through NumberOfGrids) and all have the same CellID pointing to the appropriate row in the new table.
If that sounds garbled, it probably is. Back to the second sentence in this post- It's just a glorified junction table. I was thinking too hard in the context of the immediate application. The new approach is more flexible for future applications of the same tables.

The new design looks like this:

________

Columns
ColumnName | ColumnID(PK)

Rows
RowName | RowID(PK)

Grids
GridName | GridID(PK)

CellJunction
CellID(FK) | ColumnID(FK) | RowID(FK) | TableID(FK)

Cells
CellID(PK) | CellContents
_______
Mar 11 '08 #5

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

Similar topics

7
5944
by: mr_burns | last post by:
hi, is the table percent value for height used for displaying in browsers. i have a table i want to run to the bottom of the screen so it seemed best to set the height value to 100%. when i display the table, it only sizes to the elements nested inside the table (as if no value for height has been given) and not to the bottom of the screen. i have also tried appling the height attribute as a css class but no joy.
0
1427
by: 2trax | last post by:
Hi all, I am trying to mimic the unix security model (users and groups) in a web app. I have thought of two ways of implementing this, but am unsure which would be better. The first design utilises three tables, while the second utilises two tables, however one of the columns in the first table is an an array.
15
2149
by: kimi | last post by:
I have just started working on a project that is partially complete. It is an application that is using access to store test results. The test results are being stored in two Access 2000 databases. DB #1 = StudentDB DB #2 = TestResulstsDB Why are there 2 dbs? I do not know - but that is one of the tings that we will be changing. Combining all of the data into one database.
5
3756
by: Sami | last post by:
Please bear with me, and if you answer this question, please do it step by step. I am new at Access, not at all sophisticated. I am using Office XP. This will need to be read in Access for Office 2000. I am creating a database to track student athletes. I have created the following tables. The table title is to the far left, with fields under each. The common field will be the StudentID field, which is their student number assigned...
6
4854
by: jjturon | last post by:
Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID SalesManName AT Alan Time
18
3808
by: TORQUE | last post by:
Hi, Im wondering if anyone can help me with a problem. I have a form with more than 50 unbound fields. Some of the fields will be blank from time to time. This seems to be where im having trouble. I have tried keeping some of the fields bound and when I use the save button it has been saving as 2 different records. This is unacceptable. This is what I have, can anyone help me out with this?
7
4869
by: david | last post by:
I have asked this question before. But it does not work for me. Given radion buttons in the web form design page. What I did is described as follows. A panel control is dragged into the design form, and a table control is dragged into the panel from tooolbox. Add cells for the table through the properties. Now I cut a radio button, and click the table (note I can not select a cell), then right click Paste. The button is not in expected...
1
1515
by: Mark | last post by:
Hi there gurus, can you please add your 2 cents on this design? We're having trouble relating these tables in a diagram because of the keys. Is it necesary to have the references setup? I would assume yes so the forign keys can be setup. If you look at this link, you'll see our diagram. In Red are the relationships that we would like to make for referential integrity, but cannot because of the keys....
4
1976
by: yanjie.ma | last post by:
Hi, I've got a two part question on table and form design (sorry for the length but it takes a bit to explain). Our sales department uses a look-up table to help the them select the best equipment to use for a customer's needs. It basically consists of a table with the following setup: Equipment: Option1: Option2: Option3: Option 4: ... -------- ------- ------- -------- --------
54
4041
by: csolomon | last post by:
Hello: I was wondering if I could get some input on how to address a design issue, involving my composite table. I have one portion of my project complete. The following forms and reports I will add, piggyback off of my existing design. The part I have already completed allows my users to create a design sample; this is made up of several materials to create one sample. I have accomplished this using this design:
0
9796
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10789
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10504
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10544
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9327
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6951
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5624
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
3975
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3079
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.