Tagged Questions

3
votes
2answers
44 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
65 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
2answers
82 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
71 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 ...
0
votes
2answers
145 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
107 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
61 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
343 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
143 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
144 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
55 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 ...
11
votes
5answers
484 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
708 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
190 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
189 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
467 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 ...