The logical model and/or physical design of a database.

learn more… | top users | synonyms (4)

0
votes
1answer
524 views

mysql picasa like photo tags store and query [closed]

I want to make a photo website, allow users to upload photos and write 1-10 customs photo tags, like picasa. I use mysql. Tags could be words or short sentence, but total length can not over 80 ...
2
votes
1answer
7k views

Using a foreign key on multiple fields in one table

Simple question probably. For two tables. A one to many relation is created from A -> B, TeamID -> Fk_Team, however there are multiple fields that need to reference this one relationship how does ...
9
votes
4answers
7k views

How costly is opening and closing of a DB connection?

How CPU intensive is opening and closing of a DB connection (for a web app) in MySQL ... when the DB software is on localhost? ... when the DB software is on another machine?
5
votes
1answer
96 views

Database Design: Linking 'vague' entries to associations?

Experienced developer, inexperienced DBA here. I'm currently working on a project that lets users input the names of freight vehicles for particular packages. Eg: Company: White Star Line Vehicle: ...
2
votes
1answer
173 views

Is a year-to-date dimension the same as a time dimension?

If I have a table such as...: YYMM | Sales ------------ 1201 | 10 1202 | 20 1203 | 30 1204 | 40 ... then I might describe this as having a time dimension. If I wanted to calculate a year-to-date ...
7
votes
2answers
10k views

How can you have two auto-incremental columns in one table?

I have a MySQL table that contains information on invoices of a company. However, this company has two branches, and each of them has a unique invoicing sequence; a "Serie A" and "Serie B", so to ...
5
votes
2answers
2k views

Data type for currency 'type' column, e.g. usd, cat, etc.

I am setting up a MySQL database for accounting software. One of the fields is to save the currency used for each transaction. What field type would you recommend? Details: No more than 10 different ...
5
votes
2answers
226 views

Is there a name for this “restricted one to many” relationship? Can it be enforced in the DB?

In an application I'm working on, we have a kind of "restricted one-to-many" relationship. I'm wondering if this has a name, and whether it's possible to enforce at the database level. A standard ...
1
vote
1answer
445 views

Design question about student assessment data

I am designing a relational database in Filemaker Pro that will be tracking assessment data for students in after school tutoring programs. Depending on the student's grade level and skill set, each ...
3
votes
3answers
149 views

Optimisation Newbie: How much of a 'sin' is redundancy?

I've been developing for a while, but never really had to deal with DB/scaling issues before. That's suddenly changed and I've found myself in the deep end. I have 2 SQL tables, as such: VOTES ...
2
votes
1answer
148 views

ERwin Data Modeler Support

For the sake of not asking 5,000 questions here; are there any reliable sources of information about ERwin? I have to use it for a course and I have been unable to find any website or documentation ...
1
vote
1answer
482 views

Normalization: Should common columns of two tables be pulled out into a third table?

Should I further normalize my current design to eliminate redundancies? I have the following tables: empl: standard user table empl_cat: employee "category" empl_admin_cat: administrative ...
20
votes
2answers
5k views

Handling growing number of Tenants in Multi-tenant Database Architecture

Handling a modest number of customers (tenants) in a common server with separate databases for each tenant's instance of the application is relatively straightforward and is normally the correct way ...
2
votes
4answers
227 views

MySql Database solution for specific web app problem

Well, I need to create a comment system for posts that people add to my web app. This web app is just like facebook or myspace posts. Users add posts and people can comment on them. But the problem is ...
3
votes
1answer
185 views

Human relations between users

I'm involve in a development of a tiny Social Network where users must be able to establish relations between them and also have permissions over contents. For a example: I add one user as my friend ...
1
vote
2answers
633 views

Managing features for a real estate agency [duplicate]

I have a database table real_estate where the basic information is held (id, status, sku, address, building_type, sales_status) and now I want to attach features to that real_estate table. The data ...
2
votes
1answer
144 views

One table or several tables?

I have several types of writings: articles, projects, reviews, etc. I have a table containing paragraphs (fields id (INT) and partext (TEXT)). Each writing consists of several (ordered) paragraphs. ...
3
votes
2answers
594 views

City Distance Map Database Design

Well I am just a db beginner, I want to design something like this I want to keep simple as possible, well I will enter all values[Cities, Distance etc.,] manually, because my cities are limited. ...
4
votes
4answers
12k views

Supertype/Subtype deciding between category: complete disjoint or incomplete overlapping

I'm building an inventory database that stores IT hardware, such as desktop computers, laptops, switches, routers, mobile phones, etc. I'm using supertype/subtype pattern, where all devices are stored ...
6
votes
2answers
420 views

What is the name of this schema pattern

Its been a few years... but I've run into a problem-set that reminds me of a schema design pattern and I'm trying to recall the details. Essentially, the pattern consists of one database which is 3 ...
2
votes
0answers
339 views

How to seperate sensitive data in database(MySql) [closed]

I need to design a database that will contains information about personal disease of users. What can be the approach in order to implement the columns of the DB's tables: encrypt the information, ...
2
votes
2answers
2k views

Model a family tree database in Access 2007

I designed my database with the intention of implementing it with MySQL, but have now decided to implement it in Access 2007 using form and subforms. While creating my UML diagram, I realised I have ...
2
votes
2answers
900 views

mysql products db design

I am building a products site with php and mysql(no shopping cart). I have 400k products, the main site with 2 methods for query. query for products types, all the query by menu, cloud tags (no ...
4
votes
3answers
972 views

How do I design a database for continents, countries, regions, cities and POIs?

I am not a database designer so I'm having trouble designing database with GIS information. The goal is to create a system with continents, countries, regions (including states, sub-regions, ...
2
votes
1answer
240 views

New table or new column

Apologies in advance if this is a dumb question. Background: I am a student intern, I've been given the task of adding a new feature the companies client administration system. Basically it involves ...
6
votes
1answer
5k views

2NF decomposition + database normalization

If anyone knows about 2NF I'd greatly appreciate if you could tell me if my understanding of it correct my book does not even mention it (except to say is of "historical significance") and I haven't ...
2
votes
1answer
5k views

Concept to keep track of changes in all tables

In my current project I use about 10 MySQL tables (Doctrine as DBAL). I'd like to keep track of all changes in the databases (insertions, updates and deletions). At the moment there are two ...
2
votes
1answer
1k views

How to create a train schedule in SQLite3?

I am interested in what a SQLite3 database schema would look like for a train timetable. Generally this is presented in table format as the final result. Sample Schedule for M to F STATION A ...
4
votes
1answer
171 views

Beginner design of biological sampling database

As a complete newcomer, I would like to practise designing a database based on the biological data my lab has been accumulating over the years. Here is an explanation (I am simplifying/abstracting ...
5
votes
1answer
3k views

Survey database design: first version. Are there errors?

I have to represent surveys with questions like these ones: Studying those groups of questions I found these rules: A survey has questions' groups. A questions' group has a name and questions ...
1
vote
1answer
233 views

Choosing the right field data type in SQL Server 2008

I got a request to create a new table that will store certain information for customers. The definition of the table is somewhat irrelevant except for one field. This field will store ANSI characters ...
5
votes
2answers
3k views

What is the best identifier for a UserID? 64 bit integers, UUID V5, or 64 character SHA256 UID?

I wish to future-proof my application. What is the best type of identifier to use? I am considering: 64 bit Integer UUID 64 character SHA256 I am not developing a distributed system, so is ...
11
votes
5answers
10k views

Survey database design: associate an answer to a user

I'm doing the conceptual model for a survey database. The goal is store the answers given by users (it going to be an Android app). I have three entities: user, question and option. A question will ...
3
votes
3answers
427 views

How to prevent redundant relationships in data?

If I want to link users with other users. Would this be a good way to do it: I'm asking because I probably get double data like this if i don't do specific checks: +------+-------------+ ...
6
votes
1answer
1k views

Designing a database for a site that stores content from multiple services?

I'm building a site that implements David Allen's Getting Things Done that pulls in your email, Facebook newsfeed, tweets from those you follow on Twitter, and more services are planned. The problem ...
4
votes
2answers
3k 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 ...
6
votes
1answer
391 views

Dimension Help - Deciding Fact or Dimension

We have the following Dim and Facts: Customer Dim: SCD Type 2, Info about the customer ie first purchase date, name, address, etc Product Dim: SCD Type 2, about our products Customer Snapshot ...
7
votes
2answers
5k views

Bit vs. Boolean columns

Given that bit fields are just binary representations of data and need to be queried in slightly "strange" way. Does it actually provide any benefit using a bit field for a boolean value? From what I ...
5
votes
2answers
285 views

Group_by and having vs Joins when table is self joined 5-6 times

Now the situation due to which this question came to me. Schema is Table User_Read_Book user_id | book_id Now I want to get users who have read certain books. Say give me users who ...
1
vote
1answer
86 views

Have less tables with a lot of data or a number of tables with smaller amounts of data?

Lets say I have a number of tables that will have a type and status and I want the types/statues to be configurable by a end-user of the application (so I can't use enums here). There are 2 different ...
4
votes
1answer
211 views

Spatial data modelling tools [closed]

Is there anything like MySQL workbench for PostGIS? Something that supports SQL output and geometry types would be nice. CLARIFICATION: I am trying to implement the Land Administration Domain ...
2
votes
0answers
648 views

How do I auto-fill data in Microsoft Access 2003?

I am creating a Purchase Order Form at work using Microsoft Access 2003 so that it can be saved and archived in a database and reduce paper costs. These are my current dilemmas: I am unable to ...
4
votes
2answers
644 views

Maintaining referential integrity in a booking system

I am developing a second version of a corporate training booking system on Microsoft SQL Server 2005. I have 3 tables (simplified for this question). Table 1- CourseSize table- this determines the ...
1
vote
1answer
59 views

Handling newsletter mail subscribers data

A simple data structure for subscribers is email, name, phone, building. Currently, Building is a VARCHAR field, however data contained in the field is actually mostly numeric. How can I change the ...
1
vote
2answers
487 views

Where can I find a good tutorial or book for various MySQL performance guidelines? [closed]

Can anyone refer me a good book or a small tutorial for various use cases for database design on MySQL for high load test/high availability scenarios. It should include the following: What are ...
3
votes
3answers
521 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 ...
1
vote
2answers
3k views

Adding Row Version to SQL Server table

I have a table in a database with three columns: ID, Key and Value. Everytime I update the record, I have a trigger that sends these updates to another table and creates a new record in it, who's ...
2
votes
1answer
3k views

What is different bettween Cassandra NoSQL data model and SQL data model

can any one tell. cassandra data model and sql data model how we can define row and column with cassandra data model?
0
votes
1answer
494 views

SQL DB design to support user feeds (in application like facebook)

I have a social network server with a MySql DB. I want to show the users feeds like done in Facebook. Example - UserX now Friend with userY, userX did like on postX etc. Currently I have table: C1 : ...
7
votes
1answer
591 views

How to store and query products the price of which depends on a complex set of conditions?

I haven't decided on a database. I have expirience with MySQl and I'm intrigued by MongoDB. Let the products be packages of cable tv providers: { name: "Virgin TV", price: 20 }, { name: ...