1
vote
3answers
60 views

Best approach for Legal/Natural can have many Orders

I'm working on this model: where as you may see one NaturalPerson can have many Order and the same for LegalPerson the only difference between NaturalPerson and LegalPerson are a few so in that ...
3
votes
4answers
127 views

How do I prove that the act of removing foreign keys doesn't corrupt existing data?

TL;DR: Prove that in practice, the execution of the alter table table_name drop foreign key constraint_name statement does not corrupt existing data. The important consideration is the execution of ...
1
vote
1answer
114 views

Enforce referential integrity between two groups of data

I have two sets of records: A collection of rules. A collection of actions to perform based on those rules. What I'm trying to model is something like this: Multiple rules can be grouped ...
3
votes
1answer
78 views

Non-overlapping rectangles constrained to a boundary

I am trying to model placement of parts on a circuit board. Without any meaningful constraints, my basic schema looks like this: create table part ( part_id bigserial primary key, name text ...
3
votes
1answer
125 views

Enforcing constraints “two tables away”

I ran into some trouble modeling an electrical schematic in SQL. The structure I'd like to capture is part ←────────── pin ↑ ↑ part_inst ←───── pin_inst where "inst" is short ...
1
vote
1answer
66 views

Better way to manage data integrity, without having too many composite FK's

I have following tables: class_table (class_id = Auto Incremented Primary Key (AIPK) of this table) user_table (class_id, user_id (AIPK of this table)) course_table(class_id, course_id (AIPK of this ...
2
votes
1answer
384 views

Relationships between subtypes of an entity

I have a very big (hundred of milions rows) table which represent an entity (event) having many subtypes (different kind of recorded events). Each row is identified by an id (obj_id) and has an ...
4
votes
2answers
76 views

Table design when one data is dependant of another and the second one has repeated values

I have a products, and subproducts. Like: Product Identifier - Product Name - Subproduct Identifier - SubProduct Name 1 ProA 1 SubProA1 ...
1
vote
2answers
236 views

Foreign Key constraint on fixed value field - Ever appropriate?

I have a short (15 rows) look-up table that lists the valid values for several columns in the database. I don't think it can be considered a One-True-Lookup-Table, it is about a single definite ...
5
votes
1answer
2k views

Many to Many and Weak Entities

I have an entity that cannot exist without being defined by another, and I want this entity to participate in a many-to-many relationship. Example: An artist has an album (the album cannot exist ...
5
votes
2answers
109 views

Relationship that are optionally more specific

Forgive my title, I couldn't think of anything that accurately describes what I'm talking about. I currently have the following relationship. CREATE TABLE Events ( ID INT IDENTITY(1,1) NOT ...
2
votes
2answers
78 views

Referential Integrity - Re-using table

This is a simplified example of a design issue that I am facing: I have 3 tables: Car, Ship and Bicycle. I need to add an "activity logging" table that records user actions such as deletion and user ...
1
vote
3answers
380 views

How to model a 1..1 Composition Relationship

We have a couple of entities/relationships of the following kind: Entity Event has a TimePoint The TimePoint contains time information about the event. It belongs to that event and cannot be used by ...
3
votes
2answers
177 views

Finding violations of the symmetry constraint

Suppose I have a table Friends with columns Friend1ID, Friend2ID. I chose to represent each friendship with two records, say (John, Jeff) and (Jeff, John). Thus, each pair of friends should show up ...
1
vote
1answer
325 views

Issues on defining a PK on a nvarchar column and indexing the FK referring this PK

Someone from the team, has defined two reference tables with a few records in each, and has defined the PK of these tables on a column of type nvarchar(255). The value exists in this columns are ...
4
votes
3answers
1k views

Lookup table for a one-to-many relationship

I have a tickets table with an id that I need to associate to a lookup table where the counterpart of that data is another id that is controlled from an outside source. tickets - id - sutff lookup - ...
2
votes
1answer
204 views

Representation of circular references constraint

We have a database where we store commands and properties for devices that may be controlled via a network. For instance, "Concrete Device" is an instance of some DeviceType, which in turn contains ...
5
votes
2answers
263 views

Help with good RDBMS transactional schema design that mimicks a sports league

I need to design a data model for a typical RDBMS that will mimic the structure of your typical sports league. Architecturally my requirements are: Well normalized Transactional Application ...
0
votes
1answer
67 views

Unknown relationship

I have a problem with a database design: I have an EReport. This EReport will have a QAPCategory. User must select a QAPCategory from an set of QAPCategory. To know which QAPCategory can be ...
13
votes
5answers
2k views

Are there DBMS that allow a Foreign Key that References a View (and not only base tables)?

Inspired by a Django modeling question: Database Modeling with multiple many-to-many relations in Django. The db-design is something like: CREATE TABLE Book ( BookID INT NOT NULL , BookTitle ...
2
votes
2answers
1k views

Do I need a separate Id column for this “mapping” table?

I have a table of Producers and a table of Products, both of which are of the form: Id - int, Primary key Name - nvarchar A Producer can carry multiple Products, so I was going to create a table ...
2
votes
3answers
334 views

How to maintain referential integrity of an object graph when a node is deleted

In a relational database, if a node in an object graph is deleted, how can the object graph's referential integrity be maintained? For example: A product is deleted, how is the invoice containing ...
5
votes
2answers
219 views

How should an object that can reference an object of multiple types be modeled in a relational schema?

Apologies for the obscure question, it may make more sense with a concrete example: In my application, I may create a portfolio that contains a set of projects. However, I may also add a ...
2
votes
2answers
939 views

How to select a subset of data from a large ER model?

I have a quite large ER model in my database and lots of data. Now I would like to take a subset of this data into a different database for testing purposes. The problem is how to get a subset from ...