3NF Database Normalization
There is a report table for overdue DVD rentals. The store has multiple copies of the same DVD (they are all numbered to be identified). How can I normalize this data to meet 3NF requirements? ...
data-modeling
Data model for storing categories (multiple parent nodes)
I've the following category structure: - Transport (10) - Cars (5) - Audi (2) - BMW (3) - ... - Spare Parts (5) - Audi (5) - Audi glass (1) - Carburetors (4) - Mirrors - ... - Buses - Spare Parts (5) - Audi (5) - Audi glass (1...
Sharing referential data across multiple applications
I have 3 asp.net applications. They all have common referential data tables like Categories, Countries, Provinces, etc. My intention is to share these referential tables across the 3 applications. So I would like to know if I can achieve this using SubSonic 3.0 or any other ORM. ...
MySQL, per found record join a different parent table.
I have the following parent <-> child datamodel: (almost every line is a table, indented means child-of) consumerGoods food meat item fruit item vegetable item The child-items of meat, fruit and vegetables are in the same table (named items) because they have identical a...
How to design a circular reference to a single database table with an added relationship?
I'm not sure how best to phrase the question, but essentially I have a table of contacts, and instead of doing the typical -- a contact has a reference to a table with spouse information, and a table with children, I want each of those people to be a contact, but then define a relationship between those contacts (brother, sister, child, ...
Python data structure/object to model static multidimensional table
I'm just getting back into coding after a few year hiatus and I'm trying to model multi-tiered static forms in a way that lets me grab and perform operations on a specific form level or an entire sub-tree. Example Form hierarchy: MyForm Question 1 Part 1 Question 1.1 Part 2 Question 2.1 SubPart 1 Question 2.1.1 Question 2.1.2 Q...
Model Relationship Problem
I am trying to calculate the average (mean) rating for all entries within a category based on the following model associations ... class Entry < ActiveRecord::Base acts_as_rateable belongs_to :category ... end class Category < ActiveRecord::Base has_many :entry ... end class Rating < ActiveRecord::Base belongs_to :...
Duplicating / Unnecessary data in a SQL database.
When designing a new application/database for multiple users, it usually involves having a table for user management. The table usually always has Username as well as ID.... Just to check that I am not being an idiot (well, you may still think that!) I have just downloaded and taken a look at the schema of both MediaWiki and PHPBB, and...
What is the proper object relationship? (C#)
Hi, I had a quick question about the proper object relationship I should set up for this situation: I have a Customer object with associated parameters and a depot object with associated parameters. Each depot serves a set of customers and the customer needs access to particular information for their respective depot. I'm wonderin...
how to enforce a database self join (or is there a better way)
I have an employees table in a database, and I want to link an employee to their manager: Employee Table employee_id first_name last_name manager_id If the manager_id is just another row in the same table where the manager has it as their employee, what is the best way to enforce that if I delete an employee it verifies that this ...
What should I name a table that maps two tables together?
Let's say I have two tables: Table: Color Columns: Id, ColorName, ColorCode Table: Shape Columns: Id, ShapeName, VertexList What should I call the table that maps color to shape? Table: ??? Columns: ColorId, ShapeId ...
Mapping vs. service layer, or business logic position
I got a Product and a collection of Payers. The Payers can pay for the product in three different ways, either but manually set the percentage, by Payers income or by the value of the Payers respective holdings.How the product is paid is determined by an enum on the Product. In my persistence layer i got three classes, Product, Payer, a...
Database Tables, more the better?
Lately I've been rethinking a database design I made a couple of months ago. The main reason is that last night I read the databse schema of vBulletin and saw that they use many, MANY, tables. The current "idea" I'm using for my schema, for instance my log table, is to keep everything in one table by differencing the type of Log with an...
Oracle Data Modeler and synonyms
Hi, I've been searching the web for two days and still cannot find a way to generate DDL code with public synonyms. Would be very grateful for an advice. ...
Best visual modeling tool for SQL Server (Erwin alternative)
We're looking for a visual modeling tool for SQL Server that allows for forward-engineering from the model to DDL. Erwin works great but is unavailable on this project, so we're looking for alternatives, an open source program would be ideal. We also have VSTS Database Edition, but it does not appear to allow for this type of functiona...
Database design, which table has the foreign key
I have an Table USER (USER_ID, PASSWORD, NAME, ...) and an Table ACCESS_ROLES for the Users, every user can have one ACCESS_ROLE (ONE-TO-ONE). Wich table has the Foreign Key? I would put the USER_ID into ACCESS_ROLES table. Is there any best practice approach? ...
One Table Or Three For Three Separate Objects That Have Identical Data Attributes?
I am building a site for a magazine that includes three different sections- articles, blog posts, and reviews. All three of these object types have the following columns in common: title, description, headline, subheadline, body, and author. Should I store them each in their own table, or should I just create one Posts table and add a ca...
Facebook like notifications tracking (DB Design)
I am just trying to figure out how facebook's database is structured for tracking notifications. Wont go much into complexity like facebook is. If we imagine a simple table structure for notificaitons notifications (id, userid, update, time); We can get the notifications of friends using SELECT `userid`, `update`, `time` FROM `noti...
How to model Data Transfer Objects for different front ends?
I've run into reoccuring problem for which I haven't found any good examples or patterns. I have one core service that performs all heavy datasbase operations and that sends results to different front ends (html, silverlight/flash, web services etc). One of the service operation is "GetDocuments", which provides a list of documents bas...
Should I split the data between multiple databases or keep them in a single one?
Hi, I'm creating a multi-user/company web application in php & mysql. I'm interested to know what the best practice is with regards to structuring my database(s). There will be hundreds of companies and thousands of users of this web app so this needs to be robust. Each company won't be able to see other companies data, just their ow...