a USER is a PERSON and a PERSON has a COMPANY - user -> person is one-to-one, person -> company is many-to-one. person_id is FK in USER table. company_id is FK in PERSON table. A PERSON may not be a USER, but a USER is always a PERSON. If company_id was in user table, I could create a unique key based on username and company_id, but i...
Hi all. I have a coding problem where I have to store a 'note' and the dates the note applies to. Think of: Note 1 Hi, today Mr Client rang arranging these appointments. 30/8/2009, 31/8/2009, 5/9/2009 Note 2 Business as usual. 30/8/2009 Note 3 Restaurant is shut. 6/9/2009 I need to store the following data in a database, while maint...
A common approach to data modelling is to use a data modelling tool (e.g. Erwin) to generate a schema, and then from the schema to generate business objects using an object relational mapper (ORM). Less common is the reverse process where the data modelling is done using business objects (e.g. POCO/POJO's), from which the schema is gene...
Where can you get terrain data? What resolution data can you get for free? What resolution data can you get when you buy the data? How much is that data? I found this site that has free terrain data. The resolution is 30m x 30m. Is that the best that can be found for free? ...
I'm looking for some ideas on methods for persisting customer configurable data in a relational database (SQL Server 2000 in my case). For example let's say you have a standard order entry application where your customer enters a product they want to buy. In addition to the fields that are important to both you and the customer (item...
I have events and photos, and then comments for both. Right now, I have two comments tables, one for comments related to the events, and another for photo comments. Schema is similar to this: CREATE TABLE EventComments ( CommentId int, EventId int, Comment NVarChar(250), DateSubmitted datetime ) CREATE TABLE PhotoComments ( C...
So I have an interesting problem that's been the fruit of lots of good discussion in my group at work. We have some scientific software producing SQLlite files, and this software is basically a black box. We don't control its table designs, formats, etc. It's entirely conceivable that this black box's output could change, and our design...
Lets say I have a website that sells widgets. I would like to do something similar to a tag cloud tracking best sellers. However, due to constantly aquiring and selling new widgets, I would like the sales to decay on a weekly time scale. I'm having problems puzzling out how store and manipulate this data and have it decay properly over ...
I want to build a back end of a general web form which contains questions which the user can answer. There are multiple types of questions. "Normal" text question: the question is answered via a simple text field. Example: General personal info, like the name. Textarea question: the same, but with more space to write the answer. Exampl...
I would like to know which is the common practice for a domain implementation. Designing the business objects first, that need persistence or the database schema first, generating it from an entity relationship diagram (and afterwards the ORM poco*'s)? I am going to start a solution, but I would like to know which is the most preferabl...
I am writing a standard database-backed business application. Assume that I work with bank accounts as well as "Decisions". A decision is a choice the user takes of moving money from one account to another. Each decision is taken at a particular date. A decision can have one or more "source accounts", and each "source account" will have ...
I have a problem creating a database schema for the following scenario: (I’m not creating a dating site but just using this as an example) A user logs on to a dating site and is given a multiple selection for the hair colour they’d like their date to have: This is easy enough to model with the three tables below: Tables: User {key}...
Most questions about tenancy are centered around multi-tenancy database design issues. I want to know about single tenancy but multiple applications. The software I'm developing allows for a single user to create, from a single code base, multiple applications(I call them "sections"): user could create a blog inside domain.com/applicat...
I have a many-to-many relationship defined in my Symfony (using doctrine) project between Orders and Upgrades (an Order can be associated with zero or more Upgrades, and an Upgrade can apply to zero or more Orders). # schema.yml Order: columns: order_id: {...} relations: Upgrades: class: Upgrade local: order_id ...
I have some good books on database theory (covering relational calculus, tuples, normal forms etc.) but I'd like a nuts-and-bolts text on appropriate database design: appropriate schema design choices and practical issues that come up in the field. Not so much theory oriented but from the perspective of being in the trenches. Could y...
Say I offer user to check off languages she speaks and store it in a db. Important side note, I will not search db for any of those values, as I will have some separate search engine for search. Now, the obvious way of storing these values is to create a table like UserLanguages ( UserID nvarchar(50), LookupLanguageID int ) but the s...
What is the best way to design the schema for the following requirements? Need to store Countries, States, Counties, and counties can be divided into regions. Regions can then have people with various data points to report on. Regions can also be further divided into divisions which is like grouping people. So Region 1 can have Divis...
I'm trying to present MySQL server data in a silverlight client and my current plan of attack is to use Entity Framework to MySQL with RIA Services providing the data access between the Silverlight Client and Entity Framework. However, I'm only trying to present data to the user and I do not want the possibility of me or someone else be...
I have three tables: Users, Companies and Websites. Users and companies have websites, and thus each user record has a foreign key into the Websites table. Also, each company record has a foreign key into the Websites table. Now I want to include foreign keys in the Websites table back into their respective "parent" records. How do I d...
I am writing a web application, that is US specific, so the format that other countries use for postal codes are not important. I have a list of us zip codes that i am trying to load into a database table that includes the 5 digit us zip code latitude longitude usps classification code state code city the zip code is the primary ke...