Howdy, Here is a simplification of my database: Table: Property Fields: ID, Address Table: Quote Fields: ID, PropertyID, BespokeQuoteFields... Table: Job Fields: ID, PropertyID, BespokeJobFields... Then we have other tables that relate to the Quote and Job tables individually. I now need to add a Message table where users can recor...
Hello. I have a really big database (running on PostgreSQL) containing a lot of tables with sophisticated relations between them (foreign keys, on delete cascade and so on). I need remove some data from a number of tables, but I'm not sure what amount of data will be really deleted from database due to cascade removals. How can I check...
Hi! Google results on this one are a bit thin, but suggest that it is not easily possible. My specific problem is that I need to renumber the IDs in two tables that are related to each other such that table B has an "table_a_id" column in it. I can't renumber table A first because then its children in B point to the old IDs. I can't re...
Given the schema: MACHINE_TYPE { machine_type } MACHINE { machine, machine_type } SORT_PLAN { sort_plan, machine_type } SCHEDULE { day_of_week, machine, sort_plan } and the business rule: A sort plan can be assigned to any machine of the same machine_type. How do I enforce that, in SCHEDULE, the tuples referenced by machine a...
Given the schema PERSON { name, spouse } where PERSON.spouse is a foreign key to PERSON.name, NULLs will be necessary when a person is unmarried or we don't have any info. Going with the argument against nulls, how do you avoid them in this case? I have an alternate schema PERSON { name } SPOUSE { name1, name2 } where SPOUSE.na...
Hi there, I'm piecing together an image website. The basic schema's pretty simple MySQL, but I'm having some trouble trying to represent possible admin flags associated with an image ("inappropriate", "copyrighted", etc.). My current notion is as follows: tblImages ( imageID INT UNSIGNED NOT NULL AUTO_INCREMENT, ... ); tblImag...
What is the best way to populate records in two tables that each need a reference to the primary key of the other? My thoughts are either having a "link" table between them which is populated once both have been written to the db or by the following complex series of commands Insert1 get identity1 Insert2 get identity2 update 1 How...
I have an application where the majority of the database tables have a strong relationship to one other table. Currently I am enforcing referential integrity with foreign keys, but I'm wondering if this is really the best approach. Data in the primary table can be deleted from an admin interface by business users, which means having to d...
I'm trying to understand rules of relational model as originally defined by Edgar Codd in 1970. Specifically I'm interested whether referential integrity is part of his relational model or not. I'll try to demonstrate on following example (just to make this question pretty): Customers +------+------------ | Name | Address |------+----...
Situation is pretty serious, we have a table in DB2 on AS400 which has defined foreign key to another table, so we are entering record which have regular ID of referenced table so when we enter SQL insert through front end tool everything went fine. Problem arises when this insert is to be done through java application which uses Sprin...
If I define a Customer and Order model in which a Customer "has many" Orders and the Order "belongs to" the Customer, in Rails we talk about Order having a foreign key to the Customer through customer_id but we don't mean that this is enforced in the database. Because Rails does not define this as a database-level constraint, there is ...
Hi, I've checked everything for errors: primary key, uniqueness, and type. Access just doesnt seem to be able to link the 2 fields i have in my database. can someone please take a look? http://www.jpegtown.com/pictures/jf5WKxKRqehz.jpg Thanks. ...
I am unable to define referential integrity relationship using Zend Frameworks table relationships as documented in the manual. I have exhausted every possible configuration (so it seems), and now am facing the prospect of developing the application without being able to use cascading deletions, which is undesirable. Can anyone see wh...
My problem getting Zend Framework to provide a DRI layer can now be summarized as such. Using the class definitions below I am able to delete the user but not the related comment through my local UserController "public/users/delete/userId/22", even though I have set up a refernece map and table relationship definition. Does anyone hav...
I ponder this question from time to time, so I thought I'd ask you guys about it. Let's say I have a database table that looks like this: Table: Visibility Id Value -- ----- 0 Visible 1 Invisible 2 Collapsed This is just a table for ensuring referential integrity. It is basically an enum stored in the database for the pu...
My company uses ElevateDB in one of our products. However, our database is just a bunch of tables. Recently, as more features are designed and more modules are added, we have found out our data is no longer “bunch of unrelated tables”, but it begins to be real data structure. I have no previous experience with ElevateDB, I worked with O...
I'm trying to implement a tree like structure using a Materialized Path model described here: http://www.dbazine.com/oracle/or-articles/tropashko4. Is it possible to enforce referential integrity on the [path] field? I don't see how SQL could do it, do I have to do it manually in the DAL? ...
I recently read in Fowler's PofEA that some database engines allow you to defer RI checks until the end of the transaction. Is this possible in SQL Server? The problem comes with some scenarios where I am trying to save changes that include insertions and deletions, it can be tricky to determine the correct order in which to perform th...
If I have a legacy database with no referential-integrity or keys and it uses stored procedures for all external access is there any point in using nHibernate to persist entities (object-graphs)? Plus, the SP's not only contain CRUD operations but business logic as well... I'm starting to think sticking with a custom ado.net DAL would ...
Consider a dimensional model with fact tables like (fk_dim1value, fk_dim2value, ..., value) where the fk_X columns are foreign keys into corresponding trivial dimension tables dim1value (id, value), dim2value (id, value), etc. These fact-and-dimension tables are collected automatically from disparate sources, so there's a lot of them .....