In an Ms-Access MDB, will it save disk space to limit the size of variable-length text fields? eg. If I have a variable-length Text field of size 20 and all the actual values of the field are under 10 characters, am I wasting space? Would it be better to set the size of the field to 10, or does it not make any difference? ...
I have two parent tables, BusinessGroup and SocialGroup, and one child table, Members. A Member can belong to either parent, but not both. As far as I can see, there are two options for constructing the child table. Opt 1: Include a field for ParentType, and another for ParentID. The ParentType would be an enum (Business, Social) and...
I’m interested in learning which technique developers prefer to use to enforce uniqueness in SQL Server: UNIQUE CONSTRAINT or UNIQUE INDEX. Given that there is little difference in the physical implementation of each, how do you decide which is best? Are there reasons other than performance to evaluate the best solution? Are there da...
What are the situations when you would use a foreign key to a separate table rather than use a boolean (i.e. BIT in SQL Server) For example would you replace the following two booleans in this table: engine_sensors -------------- id (int, primary key) name (varchar(50)) fault_code (int) display_warning (boolean) /* if fault show driver...
My User table (mysql db on a other server) has a FaceBookId Column. How do i filter it by friends? I mean Select * from User where FacebookId in SomeTypeOfGetFriends(of me ... the logged in user) what's the best way? should I first get the friends in an array, then do a FacebookId IN (array) ? OR: For every user that logs on, I co...
I have to build an application for my university that will count as course credit for a Class that lasts 1 month. In this application I have to have a way for users to save a Teacher Class Followup Evaluation, which is a person goes to the classroom and checks out the teacher and ticks certain columns. An example would be: Pedagogical ...
I have the following table structure in my database: create table Encargado( ID int primary key, Nombre varchar(300), ) create table Area( ID int primary key, Nombre varchar(300), Jefe int foreign key references Encargado(ID) ) create table Carrera( ID int primary key, Nombre varchar(300), Area int foreign key references Area(ID) ) c...
I'm quite a beginner at database design. I have two problems that I'd like to solve using a database, but am not sure how to deal with an unknown number of fields. Here are the two types of data I'd like to store (they represent the same schema issue I think): I want to store a bunch of coordinates for a map route. The proposed DB woul...
I'm designing a game where a character has many items and those items can be of many types. There is a character table, and twelve different tables of possible items broken down by type (such as weapons, armors, and various other item types). I want to make a table to hold instances of these item types (basically a characters' items ta...
ok im new to database design, please give me advices about this. 1 http://stackoverflow.com/questions/1823685/database-when-should-i-use-a-composite-index im not sure what does index does, but i do know we should put it when it will be heavly loaded like for WHERE verified = 1 and in search like company.name = something. a...
I have seen in my past experience that most of the people don't use physical relationships in tables and they try to remember them and apply them through coding only. Here 'Physical Relationships' refer to Primary Key, Foreign Key, Check constraints, etc. While designing a database, people try to normalize the database on paper and kee...
I have this escenario... I have three 4 entities {orderItem, suppliers, supplier, prices}, and they are related like: OrderItem 1->* suppliers (some one item can have many supplier prices) Suppliers 1->1 supplier Suppliers 1->1 itemPrices and it's properties OrderItem {orderId, partId, quantity, suppliers} Suppliers {orderId, part...
Hi, I have a question regarding DataBase design. I have a Entity Table named: CARS and I have a Lookup Table named: COLORS Table CARS has: Id, Name and Table COLORS has just: ColorName Table COLORS has a short list of colors, instead CARS could have a long list of entities. CARS has a Primary Key on ID. My questions are: Adding a ...
Hi, I'm designing a new DataBase that at the moment has 50 Tables. For many of that tables I have to eventually store a "note" for every column of every tuple. For now I found only 2 solutions: For each column of tables that needs note add a "column note" so for example: create table tab (id integer, A varchar(100), B varchar(200)) ...
I'm developing a PHP web app that handles information about certain companies. So far, the DB design looks like this: CREATE TABLE `group` ( `group_id` int(10) unsigned NOT NULL auto_increment, `group_name` varchar(50) NOT NULL, PRIMARY KEY (`group_id`) ) ENGINE=InnoDB; CREATE TABLE `company` ( `company_id` int(10) unsigned NO...
I'm struggling with how to model a resource and its availability using the tools, gems or plugins available to us in Rails. Basically each resource will have a typical availability of 0..24 hours for each day (0,1,2,3,4,5,6) of the week. BUT then there will be exceptions that need to be considered for example a holiday or special event ...
Hello, What are the adverse effects of having too many lookup tables in the database? I have to incorportate too many Enumerations, based on the applications. What would experts advice? ...
Hi, I am having below database schema. Table:items item_id int(11) autoincrement not null item_name varchar(22) not null version_start_date datetime not null version_end_date datetime The primary key for above table is (item_id,version_start_date). The semantics of version_start_date and version_end_date is to keep track of any chang...
I am looking for an SQL database of some type that is entirely based around the concept of time, just like those that are geo-centric, relationship-centric, etc. My main wish is that I can make changes to the database as I go along, and then at any point run a query that essentially says, show me how the database looked at this date and...
I am developing an app that needs to match people together. Each person can only match with one other person.. So, in the table below I am trying to make it so that values from user1 & user2 are unique across both fields: CREATE TABLE `match_table` ( `user1` int(11) NOT NULL, `user2` int(11) NOT NULL, UNIQUE KEY `user2` (`user2`...