If I have two tables - Logins and Users, as follows: Logins LoginIdNo UserIdNo HashedPassword Users UserIdNo LoginIdNo Username Each login "has a user" and each user "has a login" but which "belongs to" which? Is it simply a judgment call or is there a clearcut formula for determining where the foreign key should reside? ...
My original question can be found here, for which I've gotten some great answers, idas and tips. As part of a feasibility and performance study, I've started to convert my schemas in order to version my data using those ideas. In doing so, I've come up with some kind of other problem. In my original question, my example was simple, wit...
I use a table GadgetData to store the properties of gadgets in my application. There gadgets are basically sort of custom control which have 80% of the properties common like height, width, color, type etc. There are are some set of properties per gadget type that the unique to them. All of this data has to store in database. Currently I...
I have users for my application with access control list (these are both tables/schema/objects). Currently these are read from the database, Boolean values are used to indicate what they can view/manipulate. However, anyone can still go to the database and change the data. Can someone offer some suggestion on what kind of things I can do...
I have a script that generates DDL scripts to define materialized views for a normalized database. Some tables have columns like "owner" that point to a particular database user, which I can then create views for that will show only the rows of a table that the current database user created. Such views in some cases would be beneficial...
I am moving old project that used single table inheritance in to a new database, which is more structured. How would I write a SQL script to port this? Old structure I've simplified the SQL for legibility. CREATE TABLE customers ( id int(11), ... firstName varchar(50), surname varchar(50), address1 varchar(50), address2 v...
Its been a while since my Database Design classes in my sophomore year at Uni. and I haven't done any designs in the interim so my skills are at best rusty at the moment. I have begun working on a personal project involving the railway timetable system and seem to be stuck at the table design which resembles something like this - Stati...
Here at work (a multi-billion dollar manufaturing company with a 12 person Windows development team) we are about to go to a single master database for all new applications and will have it broken up with schemas for what we normally would have had databases for before. There will also be a few common schemas with stuff like employee dir...
Hi, I've got these tables: **Sites** :has_many :blogs :has_many :pages **Blogs** :belongs_to :site **Pages** :belongs_to :site :belongs_to :blog Basically, I want to be able to create Pages that are either related to a Site OR related to a Blog with routes like this: /blogs/1/pages/1 /sites/1/pages/2 With my current setup, my Pa...
I'm looking for something I can reference in designing the database for a contact management system. Any suggestions? ...
How would I call a Stored Procedure that returns data in a View? Is this even possible? ...
Hello, this is my sql problem - there are 3 tables: Names Lists ListHasNames Id Name Id Desc ListsId NamesId =-------- ------------ ---------------- 1 Paul 1 Football 1 1 2 Joe 2 Basketball 1 2 3 Jenny 3 Ping Pong 2 ...
I'll try to keep this example as simple as I can. I'm trying to implement a domain-driven design (a business object to represent each table) in an old VB6 app which follows no OOP patterns. The existing code is written just as you might expect from a 10 year old VB app (i.e. use of ADODB.Recordset with no intellisense for fields). The e...
When designing a database, what decisions do you consider when deciding how big your nvarchar should be. If i was to make an address table my gut reaction would be for address line 1 to be nvarchar(255) like an old access database. I have found using this has got me in bother with the old 'The string would be truncated'. I know that ...
I'm working on something of a wizard-type application to allow users to build simple "scripts" that basically perform actions based on certain conditions. Scripts that they build will be stored in a database, and modification will be common, so some sort of forward-only text generation is not an option. My program converts this internal ...
I am currently messing around with some stuff for an idea for a site - where I pretty much want to enable my users to create "Tables" which holds data and then allow them to query over this data (in a less geeky way than writing up SQL Queries and hopefully easier than using excel). My idea, so far, is to represent this in my database u...
My current understanding is that Microsoft does not offer a viable tool for creating a DB ERD model and then forward-engineering the DDL to create the database. I've heard that Visio Enterprise Edition 2003 had this feature, but that it has been removed in subsequent editions, and that the old edition does not operate well when installe...
I'm curious about what be the best way to model this for optimized performance... not as concerned about real time data integrity I'll continue with the stackoverflow example Question id title Votes id user question A question has many votes For many queries however, we're only concerned with the aggregate number of votes ...
Data in one table is referenced by ID to another Table. For example, I have a table of States with ids 1-50. In another table "User" I have a reference to that id ie. state= 4. When I need to update data back to "User", if the state changes, should my code be aware of the numbering of the state data? In other words, if the new state i...
I recently inherited a warehouse which uses views to summarise data, my question is this: Are views good practise, or the best approach? I was intending to use cubes to aggregate multi dimensional queries. Sorry if this is asking a basic question, I'm not experienced with warehouse and analyis services Thanks ...