which tools for drawing entity relationship diagram online and share with colleagues?
Hi all, Is there any website that I can use a demo version to draw an entity relationship diagram and share it with my colleagues? Thank you! ...
database-design
Assign multiple categories to one DB object in ASP.net MVC
Currently I have a database object, "vacancies" which has a column (foreign key) "CareerLevels". The table "CareerLevels" in my DB has a dynamic amount of CareerLevels, say 3 for example. Now I have my Vacancy creation wizard where, at a certain point, I have a page with some checkboxes for the CareerLevels. I want to be able to assig...
Should NULLS be handled in code or in the database? Advantages and Disadvantages?
I have several questions regarding where to handle nulls. Let me set up a scenario. Imagine I have a table that has 5 varchar(50) columns to use as an example when providing reasons for using nulls or empty strings. Is it better to handle NULLS in code or in the database? By this I mean, is it better to assign an empty string to a var...
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...
production schema
Hi, Can someone help me in understanding the difference between production schema and datawarehouse schema. Thanks in Advance Manoj Bhattu ...
Constraints instead Triggers (Specific question)
I read here some reasons to use constraints instead of triggers. But I have a doubt. How can be assure (using only constraints), the coherence between SUPERCLASS tables and SUBCLASSES tables? Whit a trigger is only a matter of check when INS.. UPD... Is there a way to define that kinda relation by using only constraints (I'm newbie at ...
Why is it Beneficial to put Questions and Answers in the same Database table?
I have learned that SO has put questions and answers in the same table. They say that a question will not have a parent id and answers will have parent IDs. Why is it better to do it this way instead of putting questions and answers in separate tables? ...
Data Structure problem, don't want to store a list as text
All - I need some help designing a table for a Postgre SQL database. I have a table of products ie; CREATE TABLE products ( product_id integer PRIMARY KEY, product_name text, price numeric); INSERT INTO products (product_id, product_name, price) VALUES (DEFAULT, 'Purple Widget', '5.50'), (DEFAULT, 'Green Widget', '1.5...
Relational database tables
Hello, I'm currently working on an ASP.Net MVC project for a software engineering class. My goal is to create a small online game rental system. I currently have a 3 tables, Movies, Games and Registrants; and I'm using LINQ-to-SQL to define each of these tables as classes for my model. So far I've created models for Movies and Games, ...
T-SQL database design and tables
Hi guys, I'd like to hear some opinions or discussion on a matter of database design. Me and my colleagues are developing a complex application in finance industry that is being installed in several countries. Our contractors wanted us to keep a single application for all the countries so we naturally face the difficulties with differ...
What datatype should I use for my column? [MySQL]
I am working on a content management system at work. I will have a column to hold the body of our users' content (HTML) and I'm not too sure what column type to use. I don't want to arbitrarily assign a maximum length but I don't know if using a LONGTEXT or BLOB field is overkill. Maybe someone could give me a tip or point me in the dir...
designing mySql index and primary keying for efficiency
I have a mid-size collection of records --about 20 million -- that I need to load into mySQL for use in data analysis. These happen to be records of people visiting places. They are uniquely identified by three data items: place - a unique INT person - a character string, sometimes numeric and sometimes alphanumeric , eg AB123456...
What should table structure look like when adding open auth login to User model
I have a Rails app with a User model. I want to allow users to login with a handful of services. There are 2 ways that I can think to store this in the database but I'm not sure which is best. I should note that I would like users to be able to connect with more than one service and link the accounts. Method 1: For each service, add ...
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 ...
trying to inner join multiple tables but always returning 0 rows.
I am trying to figure out why this query returns 0 rows as there is data in all 3 tables. Here are my tables: Table1: Applications Columns: ID, Name Table2: Resources Columns: ID, Name Table3: ApplicationResourceBridge Columns: ID, app_id, resource_id And Here is the query SELECT Resources.name , ApplicationResourc...
Tips for designing a minimalist data store suitable for an embedded device.
I am trying to design a data store implementation like SQLite, BerkeleyDB or CouchDB suitable for a small embedded computer platform. The main criteria is minimal bloat and simple API. I could not find any products that fit my needs. SQLite is a tad too large and mimics relational databases. CouchDB has a good RESTful API but is bloated...
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 ...
Database: Best performance way to query geo location data ???
I have a MySQL database. I store homes in the database and perform literally just 1 query against the database, but I need this query to be performed super fast, and that's to return all homes within a square box geo latitude & longitude. SELECT * FROM homes WHERE geolat BETWEEN ??? AND ??? AND geolng BETWEEN ??? AND ??? How is the b...
whats the best way to model dependencies in SQL relational database
if i have a table called projects and a project can have a dependency on one or many other projects, what is the best way to model this in db tables. i have one table called Projects with columns: ID, Name i was thinking of having a new column called Dependencies and just store the ID of another project but i just realized that there c...
MySQL database structure: more columns or more rows?
I'm creating an online dictionary and I have to use three different dictionaries for this purpose: everyday terms, chemical terms, computer terms. I have tree options: 1) Create three different tables, one table for each dictionary 2) Create one table with extra columns, i.e.: id term dic_1_definition dic_2_definition dic_...